SQL 删除重复记录且保留一条
面试 SQL MySQL About 522 words尝试解决
尝试一
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
);
Views: 1,887 · Posted: 2019-04-07
————        END        ————
Give me a Star, Thanks:)
https://github.com/fendoudebb/LiteNote扫描下方二维码关注公众号和小程序↓↓↓
Loading...