Geek Answers Handbook

MYSQL Delete FIRST Duplicates from a Table

I have 511 duplicates in my table, I need to delete them, but not the originals. Therefore, I want to remove every second occurrence of each duplicate.

How can i do this?

Table:

code / status / time E3F4FF928A / 0 / 07D95444BB / 0 / 07D95444BB / 0 / 40006C128F / 0 / 1315293012 2B45790E52 / 0 / 40006C128F / 0 / 1315293012
+8
sql mysql
source share
3 answers

add a unique index to the column table, which should be unique, and ignore errors

 alter ignore table X add unique index (column_a)

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

+8
source share

Just run this problem today (there is no unique key in the table).

I solved it like this:

 DELETE FROM table where code=x and status=y and time=z LIMIT 1;

This will delete the first 1 row for the given criteria (if you have n duplicates, put n-1 to save only one).

+1
source share
 DELETE t2 FROM table1 t2 INNER JOIN table1 t1 ON (t1.somefield = t2.somefield AND t1.otherfield = t2.otherfield /*add more fields if need be */ AND t2.id > t1.id) LEFT JOIN table1 t3 ON (t1.somefield = t3.somefield AND t1.otherfield = t3.otherfield /*add more fields if need be */ AND t3.id > t2.id) WHERE (t3.id IS NULL) ORDER BY t2.id ASC

This should remove only the 2nd duplicate and leave only the 3rd and more duplicates.

If you want something less esoteric and you have a timestamp column, maybe you want to do

 DELETE t2 FROM table1 t2 INNER JOIN table1 t1 ON (t1.somefield = t2.somefield AND t1.otherfield = t2.otherfield /*add more fields if need be */ AND t2.`timestamp` > t1.`timestamp`) WHERE (1=1) /*In some mode(s) MySQL requires a where clause with delete*/ ORDER BY t2.id ASC
0
source share

More articles:


All Articles