MySQL 删除重复记录且保留一条

MySQL 大约 522 字

尝试解决

尝试一

DELETE from user2 WHERE id not in (SELECT id from user2 GROUP BY `name`);

报错

[Err] 1093 - You can't specify target table 'user2' for update in FROM clause

原因

更新数据时使用了查询,而查询的数据又做更新的条件,MySQL不支持这种方式

尝试二

DELETE from user2 WHERE id not in (
    SELECT * from (
        SELECT id from user2 GROUP BY `name`
    )
);

报错

[Err] 1248 - Every derived table must have its own alias
每个派生表都必须有自己的别名

尝试三

成功

DELETE from user2 WHERE id not in (
    SELECT * from (
        SELECT id from user2 GROUP BY `name`
    ) as id
);
阅读 507 · 发布于 2019-04-07

————        END        ————

扫描下方二维码关注公众号和小程序↓↓↓

扫描二维码关注我
昵称:
随便看看 换一批