【小记】[Err] 1093 - You can‘t specify target table ‘action‘ for update in FROM clause

[Err] 1093 - You can’t specify target table ‘action’ for update in FROM clause

今天删除重复记录的数据时使用，报错：

You can’t specify target table for update in FROM clause

意思就是 你不能又要查又要操作的
所以要用中间表select出去

错误SQL

DELETE
FROM
 check_data
WHERE
 id IN (
    SELECT
     id FROM check_data
    GROUP BY
     lot_num,
     step_name
    HAVING
     count(1) > 1
 )

修改后SQL

 DELETE
FROM
 check_data
WHERE
 id IN (
  SELECT
   dd.id
  FROM
   (
    SELECT
     id FROM check_data
    GROUP BY
     lot_num,
     step_name
    HAVING
     count(1) > 1
   ) dd
 )