使用 MS Access 查找重复项并将其删除
我正在尝试从数据库中删除所有重复项。我在这个网站上找到了下面的代码。问题是它删除了我的整个数据库。遗憾的是，我对 MySQL 的了解不够，无法解释为什么它删除了所有条目而不仅仅是重复项。
DELETE FROM RosterPool AS t1
WHERE EXISTS (SELECT 1 from RosterPool t2
WHERE t1.Rate = t2.Rate
AND t1.FullName = t2.FullName
AND t1.Last4 = t2.Last4
AND t1.Graduated = t2.Graduated);
有没有办法将所有重复项移动到另一个表并删除该表？
I'm trying to delete all duplicates from my database. I found the code below on this website. Problem is it deleted my entire database. Sadly I don't know enough about MySQL to tell why it got rid of all entries and not just the duplicates.
DELETE FROM RosterPool AS t1
WHERE EXISTS (SELECT 1 from RosterPool t2
WHERE t1.Rate = t2.Rate
AND t1.FullName = t2.FullName
AND t1.Last4 = t2.Last4
AND t1.Graduated = t2.Graduated);
Is there a way to move all the duplicates to another table and just delete that table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
SQL 数据库中不存在“移动”操作之类的东西。只有插入、更新和删除。
如果您想安全起见，您可以将删除查询转换为选择查询，以准确查看如果将其作为删除查询运行，将会删除什么，但是没有办法将行从一个表“移动”到另一个表，无需执行插入/选择+删除操作序列。
There's no such thing as a "move" operation in SQL databases. There's insert, update, and delete only.
If you want to play it safe, you can turn the delete query into a select query to see exactly WHAT will be nuked if you run it as a delete query, but there's no way to "move" a row from one table to another, without doing a insert/select + delete operation sequence.
只需在要从中删除重复项的列上强制使用唯一键即可。
为您编写的列速率添加唯一键：
如果您已经有重复的行，则会出现错误，
但是通过添加关键字 IGNORE，您可以强制它添加 uniqe 并忽略警告
just force a uniqe key on the column you want to remove duplicates from.
to add a unique key for column rate you write:
that going to give you an error if you already have duplicate rows,
but by adding the keyword IGNORE, you can force it to add the uniqe and ignore the warnings