最近在用sql执行删除操作的时候
提示MySQL报错:You can't specify target table 'user' for update in FROM clause
翻译为“不能先select出同一表中的某些值,再update这个表(在同一语句中)”,即delete的目标表不能在其直接子查询中存在,目标表在子查询中的任何位置都会报错哦
```
DELETE
FROM
`user`
WHERE `person_id` IN
(SELECT
u.person_id
FROM
`user` AS u
LEFT JOIN `job_user` AS ju
ON u.`person_id` = ju.`person_id`
WHERE ju.`person_id` IS NULL) ;
```
解决方案
在中间位置多嵌套一层子查询,delete的目标表便不在其直接子查询中存在了,则可删除成功。
```
DELETE
FROM
`user`
WHERE `person_id` IN
(SELECT
`person_id`
FROM
(SELECT
u.`person_id` AS `person_id`
FROM
`user` AS u
LEFT JOIN `job_user` AS ju
ON u.`person_id` = ju.`person_id`
WHERE ju.`person_id` IS NULL) AS tmp) ;
```