SQL Server - UPDATE table where ID is in SELECT?
I have a table where I want to update all rows with the ID that exists in the select result.
My pseudo-code:
UPDATE mytable as t
SET t.status = 'PM'
WHERE t.ID EXISTS IN (select ID from ...)
开发者_运维百科I have managed to do the select statement, now I want to use the result of the select statement to update a table.
If you remove the exists you have a valid query from what I can tell.
UPDATE mytable
SET status = 'PM'
WHERE id IN (select ID from ...)
Works for me in MySql 5.5, not sure which database you're using.
One cannot use substitution in the UPDATE statement. The original query should be good when you leave out the " as t" part and both "t.".
That works for me
#dont work
UPDATE mytable as t1 SET t1.a = value
WHERE t1.b IN (SELECT b FROM table WHERE condition);
#work
UPDATE mytable as t1 SET t1.a = value
WHERE t1.b IN (SELECT * FROM (SELECT b FROM table WHERE condition) as t2)
精彩评论