You can't specify target table 'xxx' for update in FROM clause

1、执行sql语句报上面的错误：

DELETE 
FROM
    db_student 
WHERE
    RowGuid IN ( SELECT RowGuid FROM db_student WHERE age = 22 GROUP BY RowGuid HAVING count( * ) > 1 ) 
    AND ID NOT IN ( SELECT MAX( ID ) AS id FROM db_student WHERE age = 22 GROUP BY RowGuid HAVING count( * ) > 1 )

报错如下所示
You can't specify target table 'xxx' for update in FROM clause。

原因：因为在MYSQL里，不能先select一个表的记录，在按此条件进行更新和删除同一个表的记录。

详细参考：https://blog.csdn.net/h996666/article/details/81699255

SELECT *
FROM
    db_student 
WHERE
    RowGuid IN (
SELECT
    aa.RowGuid 
FROM
    ( SELECT RowGuid FROM db_student WHERE age = 22 GROUP BY RowGuid HAVING count( * ) > 1 ) aa 
    ) 
    AND ID NOT IN (
SELECT
    t.id 
FROM
    ( SELECT MAX( ID ) as id FROM db_student WHERE age = 22 GROUP BY RowGuid HAVING count( * ) > 1 ) t 
    )
    

DELETE 
FROM
    db_student 
WHERE
    RowGuid IN (
SELECT
    aa.RowGuid 
FROM
    ( SELECT RowGuid FROM db_student WHERE age = 22 GROUP BY RowGuid HAVING count( * ) > 1 ) aa 
    ) 
    AND ID NOT IN (
SELECT
    t.id 
FROM
    ( SELECT MAX( ID ) as id FROM db_student WHERE age = 22 GROUP BY RowGuid HAVING count( * ) > 1 ) t 
    )

 

待续......