MySQL 语句报错：1093 - You can't specify target table 'table_name' for update in FROM clause, Time: 0.000000s

SQL

DELETE FROM work
WHERE id IN
(
SELECT w.id
FROM work w
LEFT JOIN user u ON w.user_id=u.id
WHERE w.url='https://www.hello.cn/works/id8353954'
AND w.user_id!=u.user_id
) 
;

 

 

报错：

1093 - You can't specify target table 'table_name' for update in FROM clause, Time: 0.000000s

 

它的意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)，即不能依据某字段值做判断再来更新某字段的值。

 

解决方案：

将SELECT出的结果再通过中间表SELECT一遍。

DELETE FROM work
WHERE id IN
(SELECT temp.id FROM
(
SELECT w.id
FROM work w
LEFT JOIN user u ON w.user_id=u.id
WHERE w.work_url='https://www.hello.cn/works/id8353954'
AND w.user_id!=u.user_id
) temp)
;

 

 

 

参考：

https://blog.csdn.net/qq_29672495/article/details/72668008