mysql删除重复数据只保留id最大一条记录

目的： 一张表，表名 credit_user

相同的 user_code有多条重复数据，现在只保留一条数据

一：首先是这么想的

DELETE
FROM
    credit_user
WHERE
    user_code IN (
        SELECT
            user_code
        FROM
            credit_user
        GROUP BY
            user_code
        HAVING
            count(user_code) > 1
    ) and  id NOT IN (
        SELECT
            min(id)
        FROM
            credit_user
        GROUP BY
            user_code
        HAVING
            count(user_code) > 1
    );

 

发现在mysql中会报错

错误信息：[Err] 1093 - You can't specify target table 'credit_user' for update in FROM clause

于是查资料说是不能先select出同一表中的某些值，再update这个表(在同一语句中) 

二：正确写法

DELETE
FROM
    credit.credit_user
WHERE
    user_code IN (
        SELECT * FROM (
        SELECT
            user_code
        FROM
            credit_user
        GROUP BY
            user_code
        HAVING
            count(user_code) > 1
          ) b
    ) and  id NOT IN (
        SELECT * FROM (
        SELECT
            min(id)
        FROM
            credit_user
        GROUP BY
            user_code
        HAVING
            count(user_code) > 1
          ) c
    );

 

也就是说将select出的结果再通过中间表select一遍，这样就规避了错误。注意，这个问题只出现于mysql

转载于:https://www.cnblogs.com/wanghongsen/p/8108483.html