I have a table where I want to update all rows with the ID that exists in the select result.
我有一个表，我想用更新结果中存在的ID更新所有行。
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.
我已设法执行select语句，现在我想使用select语句的结果来更新表。
2 个解决方案
#1
17
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.
在MySql 5.5中为我工作，不确定您正在使用哪个数据库。
#2
1
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.".
不能在UPDATE语句中使用替换。当你省略“as t”部分和“t”时，原始查询应该是好的。
#1
17
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.
在MySql 5.5中为我工作，不确定您正在使用哪个数据库。
#2
1
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.".
不能在UPDATE语句中使用替换。当你省略“as t”部分和“t”时，原始查询应该是好的。