SQL - Merge duplicate rows by the most recent

Question

I have a big SQL database with these tables for example:

first_name | last_name | email             | country | created_at
-----------------------------------------------------------------
      john | DOE       | johndoe@email.com | USA     | 2016-05-01
      john | DOE       | johndoe@email.com | FRANCE  | 2019-05-03
       doe | John      | johndoe@email.com | CANADA  | 2011-08-23

The previous database was built without a unique email (yes it's horrible). So, I need to merge the user with same email but different data with the most recent record.

Then update the database by deleting the older one and keep the latest one.

Excuse me if it's not clear..

I recommending doing a soft delete instead of a hard delete if space is not so much of a concern — Sahil Dhoked, Jan 29 '19 at 17:15
Maybe this will help? https://stackoverflow.com/questions/7238983/delete-all-records-except-the-most-recent-one — aggaton, Jan 29 '19 at 17:41

score 4 · Accepted Answer · answered Jan 29 '19 at 17:08

4

Something like this?

delete t
    where t.created_at < (select max(t2. created_at)
                          from t t2
                          where t2.email = t.email
                         );

answered Jan 29 '19 at 17:08

Gordon Linoff

1,242,037
58
646
786

Excuse me I'm not very strong with SQL, I don't understand the code. `t` is the tablename and `t2` ? – Jean R. Jan 30 '19 at 08:45
`t` is the table name. `t2` is a table alias. – Gordon Linoff Jan 30 '19 at 13:04

score 1 · Answer 2 · answered Jan 29 '19 at 17:16

With EXISTS:

delete tablename t
where exists (
  select 1 from tablename where email = t.email and created_at > t.created_at
)

EXISTS will return TRUE as soon as it finds 1 row with the same email and date greater than the current row, so it does not need to scan the whole table for every row.

score 0 · Answer 3 · answered Jan 31 '19 at 15:03

You mentioned that this is a big database. I will then suggest that you add an index on the table before running the script by either @forpas or @Gordon Linoff as these scripts might take a long time to complete when dealing with millions of rows.

The index could be created like this:

CREATE INDEX tablename_index ON tablename (email, created_at);

And then afterwards, if you don't need the index any more, you can drop it like this:

DROP INDEX tablename_index ON tablename;

SQL - Merge duplicate rows by the most recent

3 Answers3