如何删除表中重复的记录? [英] How to delete records in the table which are repeated?

问题描述

您好，在这里我遇到了一种情况，即错误地没有删除表，而是运行了该表的批处理文件，该文件由一些插入语句详细组成

Hi Here i came across a situation in which by mistakenly Without dropping the table i have run the batch file of the table which consists of some insert statements in detail

我有一个像alert_priority这样的表，其中包含诸如这样的记录

I have a table like alert_priority consists of records like

Id  priority_name
---  --------------
1     P0
2     P1
3     P2

现在错误地没有删除 alert_priority 我已经执行了表的脚本文件，该文件包含一些插入语句，现在执行脚本后，我在表中的记录就像

and now by mistakenly without dropping alert_priority i have executed script file of the table which consists of some insert statements and now after executing the script my records in the table are like

Id  priority_name
---  --------------
1     P0
2     P1
3     P2
1     P0
2     P1
3     P2

现在我要删除多余的记录(Id 3之后的记录)，并且在执行脚本文件之前，我应该拥有所有存在的记录.

Now i want to delete the records which are extra(records after Id 3) and i should have all the records which are present before i have executed the script file.

尽管我可以选择删除表并再次执行脚本文件，但我想知道有什么方法可以通过sql查询执行
表中没有primay键

Although i have an option to drop the table and execute the script file once again, I wanted to know is there any way which we can do through sql query
I have no primay keys in the table

推荐答案

首先，考虑将ID字段设置为AI(自动增加)，甚至设置为PK(主键).

First , consider setting your ID fields as AI (auto increasment) and even PK (Primary Key).

为了删除那些重复的行，我们将创建一个新表并将所有 那些重复的行. 之后，放下该表.

In order to remove those duplicated rows , we will create a new table and will move all those duplicated rows to it. After that , drop that table.

创建临时表bad_temp AS SELECT DISTINCT * FROM alert_priority

CREATE TEMPORARY TABLE bad_temp AS SELECT DISTINCT * FROM alert_priority

这篇关于如何删除表中重复的记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！