用相同的表更新查询抛出错误

Question

UPDATE
work_info as info1
SET
info1.status_id = 1
WHERE
info1.info_id IN(
SELECT info2.info_id
FROM work_info as info2
WHERE info2.info_id IN (
SELECT MAX(info3.info_id)
FROM work_info as info3
GROUP BY info3.user_license_id)
AND info2.status_id = 5)

得到这个错误#1093 -你不能在FROM子句中指定目标表'info1'进行更新

Answer 1

直接(不试图理解查询的逻辑):

UPDATE work_info as info1
JOIN work_info as info2 USING (info_id)
JOIN ( SELECT MAX(info_id) info_id
FROM work_info
GROUP BY user_license_id 
) as info3 USING (info_id)
SET info1.status_id = 1
WHERE info2.status_id = 5;

Answer 2

UPDATE table SET a=value WHERE x IN
(SELECT x FROM table WHERE condition);

因为它们是同一个表，所以您可以欺骗并执行:

UPDATE table SET a=value WHERE x IN
(SELECT * FROM (SELECT x FROM table WHERE condition) as t)

[更新或删除或其他]

https://www.codeproject.com/Tips/831164/MySQL-can-t-specify-target-table-for-update-in-FRO: ~:文本=一些% 20天% 20 % 20 % 20的写作时,选择% 20 % 20你% 20更新% 20标准。