sql语句报"You can't specify target table 表名 for update in FROM clause"

在执行一个删除语句的时候写了如下语句

DELETE FROM user
WHERE card_num IN (
        SELECT card_num
        FROM user
        GROUP BY card_num
        HAVING COUNT(*) > 1
    );

执行报错:You can't specify target table 'user' for update in FROM clause"

这是由于删除或更新会影响之查询语句,所以会报错. 正确的做法是将查询结果放入到一个临时表中

正确写法:

DELETE FROM user
WHERE card_num IN (
        SELECT *
        FROM (
            SELECT card_num
            FROM user
            GROUP BY card_num
            HAVING COUNT(*) > 1
        ) t
    );