I have a mySQL table with duplicate entries (perhaps in some cases multiple duplicates). I have column called id, which may contain duplicate ids and one called unique id which, as the name suggest contains unique ids. Using the following SQL statement I am able to select the duplicate row
我有一个带有重复条目的mySQL表(可能在某些情况下有多个重复项)。我有一个名为id的列,它可能包含重复的id和一个名为unique id的名称,顾名思义包含唯一ID。使用以下SQL语句,我可以选择重复的行
SELECT id,
COUNT(id) AS NumOccurrences
FROM `TABLE 3`
GROUP BY id
HAVING ( COUNT(id) > 1 )
but how do I delete (all but one of) them?
但是如何删除(除了其中一个)它们?
2 个解决方案
#1
2
DELETE t1 FROM test t1, test t2 WHERE t1.unique_id > t2.unique_id AND t1.id = t2.id;
#2
0
You should add a UNIQUE INDEX on the unique id field. This will prevent more duplicates from sneaking in later.
您应该在唯一ID字段上添加UNIQUE INDEX。这样可以防止更多重复内容在以后偷偷摸摸。
ALTER IGNORE `TABLE 3` ADD UNIQUE INDEX (`unique_id`);
The IGNORE bit will only keep the first row with the value "unique_id". You should also avoid using spaces in your column or table names. It makes writing SQL error prone as you have to use ticks around names.
IGNORE位仅保留第一行的值“unique_id”。您还应该避免在列或表名中使用空格。它使编写SQL容易出错,因为你必须使用名称周围的刻度。
#1
2
DELETE t1 FROM test t1, test t2 WHERE t1.unique_id > t2.unique_id AND t1.id = t2.id;
#2
0
You should add a UNIQUE INDEX on the unique id field. This will prevent more duplicates from sneaking in later.
您应该在唯一ID字段上添加UNIQUE INDEX。这样可以防止更多重复内容在以后偷偷摸摸。
ALTER IGNORE `TABLE 3` ADD UNIQUE INDEX (`unique_id`);
The IGNORE bit will only keep the first row with the value "unique_id". You should also avoid using spaces in your column or table names. It makes writing SQL error prone as you have to use ticks around names.
IGNORE位仅保留第一行的值“unique_id”。您还应该避免在列或表名中使用空格。它使编写SQL容易出错,因为你必须使用名称周围的刻度。