SQL 更新 WHERE NOT EXISTS 问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6909436/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update WHERE NOT EXISTS problem
提问by bash-
This query gives an error whenever there are more than 1 record that exists in tbl1 and not in tbl1_temp
每当 tbl1 中存在多于 1 个记录而 tbl1_temp 中不存在时，此查询就会给出错误
How can I fix this so that for every record that does not exist in tbl1_temp, update the corresponding record's status in tbl1
如何解决此问题，以便对于 tbl1_temp 中不存在的每个记录，更新 tbl1 中相应记录的状态
UPDATE tbl1 SET Status = 'Finished'
WHERE id = (SELECT id
FROM tbl1
WHERE NOT EXISTS (SELECT id FROM tbl1_temp
WHERE tbl1.id = tbl1_temp.id))
回答by Steve Mayne
UPDATE tbl1
SET Status = 'Finished'
WHERE NOT EXISTS (SELECT id FROM tbl1_temp
WHERE tbl1.id = tbl1_temp.id)
回答by gbn
Just use NOT EXISTS directly
直接使用 NOT EXISTS
UPDATE tbl1
SET Status = 'Finished'
WHERE NOT EXISTS (SELECT * FROM tbl1_temp
WHERE tbl1.id = tbl1_temp.id)
回答by Péter T?r?k
The equality operator works only for a single value, not for multiple values, hence the error. So instead of equation, use IN:
相等运算符仅适用于单个值，不适用于多个值，因此会出现错误。所以，而不是方程，使用IN：
... WHERE id IN (SELECT ...)
回答by kan
UPDATE tbl1 SET Status = 'Finished'
WHERE id **IN** (SELECT id
FROM tbl1
WHERE NOT EXISTS (SELECT id FROM tbl1_temp
WHERE tbl1.id = tbl1_temp.id))
