MySql query to remove duplicate rows while keeping the last timestamp value?

Question

I had a problem updating my mysql table. I have a table containing 5 columns including ID, NAME, TIMESTAMP and the other 2 columns. My problem is that I want to delete those lines for which both ID and NAME correspond to another line, ID and NAME, keeping the last line of TIMESTAMP.

I am trying to execute the following query, but it shows an error:

# 1093 - You cannot specify the target table 'dummy1' for updating in the FROM clause

Request:

delete from dummy1 where LAST_USED_DATE not in( select max(LAST_USED_DATE) from dummy1 group by ID,NAME); 

Answer 1

Try

 DELETE d FROM dummy1 d JOIN ( SELECT d1.id, d1.name, MAX(d1.last_used_date) max_date FROM dummy1 d1 JOIN dummy1 d2 ON d1.id = d2.id AND d1.name = d2.name GROUP BY id, name ) q ON d.id = q.id AND d.name = q.name AND d.last_used_date <> q.max_date 

Here is the SQLFiddle demo

UPDATE . To check only duplicate identifiers, you can slightly modify the above query

 DELETE d FROM dummy1 d JOIN ( SELECT d1.id, MAX(d1.last_used_date) max_date FROM dummy1 d1 JOIN dummy1 d2 ON d1.id = d2.id GROUP BY id ) q ON d.id = q.id AND d.last_used_date <> q.max_date 

Here is the SQLFiddle demo

UPDATE2 To remove cheats with a maximum timestamp

1) you can enter a unique index with the IGNORE option. In this case, MySql decides which records remain.

 ALTER TABLE dummy1 ENGINE MyISAM; ALTER IGNORE TABLE dummy1 ADD UNIQUE (id, name); ALTER TABLE dummy1 ENGINE InnoDB; 

Answer 2

Even without a violin, I give him a chance. Try the following:

 DELETE D1.* FROM DUMMY1 D1 JOIN (SELECT D.ID,D.NAME, MAX(LAST_USED_DATE) DATE FROM DUMMY1 D GROUP BY D.ID) D2 ON D1.ID = D2.D2 WHERE D1.NAME = D2.NAME AND D1.LAST_USED_DATE <> D2.DATE 

I get the opposite as you save MAX(LAST_USED_DATE)