delete mysql duplicate enty and use the most up2date one based on timestamp

Question

I have 2 entries in mysql database which has duplicate entries under 'hwaddr' column as shown in the added screenshot.

how do i search for duplicates based on 'hwaddr' column and leave only the most up2date one based on 'timestamp' column? (delete the old one)

mysql data screenshot

See this - http://stackoverflow.com/questions/6107167/mysql-select-duplicate-records-but-keep-latest — Manashvi Birla, Jun 25 '15 at 11:50

score 0 · Answer 1 · answered Jun 25 '15 at 12:08

0

Try this to delete:

DELETE FROM `list` WHERE `id`IN (SELECT *
    FROM (SELECT MAX( id )
    FROM `list`
    GROUP BY ip
    HAVING COUNT( ip ) >1) AS e)

answered Jun 25 '15 at 12:08

Suraj

363
2
16

score 0 · Answer 2 · answered Jun 25 '15 at 12:43

assuming greater number primary key (id) will have latest timestamp, so you can use below query which is more optimized and will delete all duplicate records not just one, excluding latest one.

DELETE b.* 
FROM mytable b 
LEFT JOIN (SELECT MAX(id) FROM mytable GROUP BY hwaddr) a ON a.id=b.id 
WHERE a.id IS NULL;

This is single query command even for production server there can be better options.

Asaf Magen · Answer 3 · 2015-07-07T13:38:12.550

listing hwaddr duplication

SELECT * FROM `general2` WHERE hwaddr in ( SELECT hwaddr FROM general2 GROUP BY hwaddr HAVING COUNT( * ) >1 )

listing what will be deleted

select t1.* FROM general2 t1, general2 t2 WHERE t1.hwaddr=t2.hwaddr  AND t1.timestamp < t2.timestamp

deleting by hwaddr leaving the most updated by timestamp.

DELETE t1 FROM general2 t1, general2 t2 WHERE t1.hwaddr=t2.hwaddr  AND t1.timestamp < t2.timestamp

delete mysql duplicate enty and use the most up2date one based on timestamp

3 Answers3