How can I find duplicate entries and delete the oldest ones in SQL?

Question

I've got a table that has rows that are unique except for one value in one column (let's call it 'Name'). Another column is 'Date' which is the date it was added to the database.

What I want to do is find the duplicate values in 'Name', and then delete the ones with the oldest dates in 'Date', leaving the most recent one.

Seems like a relatively easy query, but I know very little about SQL apart from simple queries.

Any ideas?

Answer 1

delete from table a1 where exists (select * from table a2 where a2.name = a1.name and a2.date > a1.date)

Answer 2

You could probably achieve this with a self-join and a IS NOT NULL.

Joining on DELETE queries can be a little dangerous, because the more complex it is the more the risk of deleting more than you intend to in some circumstances.

But I would approach it like.

DELETE
  a.*
FROM
  mytable AS a
  LEFT JOIN mytable AS b ON
    b.date > a.date
    AND (b.name=a.name OR (b.date = a.date AND b.rowid>a.rowid))
WHERE
  AND b.rowid IS NOT NULL

The join and the IS NOT NULL finds every row for which there exists a newer row with the same name. It also handles the case of two rows with the same date correctly - if they have the same date, then it goes by rowid (whatever that is).

Hopefully something like this works.