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

一、问题

执行delete语句时，报错“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) ;