I pieced this query together using some stuff I found on the web. I’m afraid to run it because once an update is done it can’t be undone. Does this look good to you?
UPDATE users u, (SELECT uID AS selected_uID FROM users) AS u2 SET u.guide = u2.selected_uID
u.member = ‘No’ and
u.guide !=110 and
u.guide not in (select uID from users);
no, it is not right
first, you are cross-joining u and u2, because you’ve forgotten a join condition
but more importantly…
Currently, you cannot update a table and select from the same table in a subquery.
– MySQL :: MySQL 5.0 Reference Manual :: 12.2.10 UPDATE Syntax
Ok, so what is the best way to update a whole lot of people based on multiple criteria?
i guess that would depend on the criteria, wouldn’t it
i’m not trying to be flippant, but you haven’t explained what those criteria are
it’s pretty hard to discern what the relationship of the guide and uid columns are (or should be)