开发者

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)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜