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

如有问题，可以QQ搜索群1028468525加入群聊，欢迎一起研究技术

有疑问联系站长，请联系QQ：