MySQL报错:You can’t specify target table ‘user’ for update in FROM clause

最近在用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) ;
```

    A+
发布日期:2023年01月14日  所属分类:未分类

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: