Delete second duplicate entry in mySql table

Question

I have a set of 2,000,000 rows in my MySQL table. Now I would like to delete the second duplicate entry in the table. (i.e.), The first record of a duplicate value should not be deleted. Delete only two duplicate entries in the table.

Example

id | logEntry | logValue -------------------------------- 1 | AA15AA7515 | 11445588 2 | AAFIRNNFIE | 99586454 3 | AA15AA7515 | 14589313 4 | AA9894AF56 | 15985632 5 | AA15AA7515 | 16124246 6 | AA69481533 | 15454186 7 | AAFIRNNFIE | 12788549 

In this example, Id (1,3,5), (2,7) contains duplicates. I would like to delete lines 3,5,7

Expected Result

 id | Entry | Value -------------------------------- 1 | AA15AA7515 | 11445588 2 | AAFIRNNFIE | 99586454 4 | AA9894AF56 | 15985632 6 | AA69481533 | 15454186 

Answer 1

 delete from temp1 where id in ( select x.id from (select a.id from temp1 a, temp1 b where a.id<>b.id and a.logentry=b.logentry and a.id>b.id group by a.id) x ); 

This will work fine.

Check here: http://www.sqlfiddle.com/#!9/825e0/1

Answer 2

You have a table with duplicate rows - somehow, a unique index was not created, and the error added duplicate records to your table.

You must modify the table by adding a UNIQUE constraint.

You can use this query as follows:

 DELETE from table1 USING table1, table1 as vtable WHERE (NOT table1.ID>vtable.ID) AND (table1.logEntry=vtable.logEntry) 

You must run this query to ensure that the table can no longer repeat entries:

 ALTER TABLE table ADD CONSTRAINT table_unique UNIQUE (field1,field2); 

but you can do it if the table is empty.

or if entries exist, try adding IGNORE

 ALTER IGNORE TABLE table ADD CONSTRAINT table_unique UNIQUE (field1,field2); 

NOTE. If you insert data using the server side of the script (it seems you are using php as tagged), use also checking for the existence of an existence record in the table.

Answer 3

try it

 Delete From table_name where id not in( select min(id) from table_name group by Entry ) 

Answer 4

This is a bit of a pain in MySQL. I would do it with left join

 delete t from table t left join (select min(id) as minid from table t2 group by entry ) tokeep on t.id = tokeep.minid where tokeep.minid is null;