#database #mysql #postgresql #sql

Today I learned a neat trick for deleting duplicate rows in a database with a single query…

MySQL

 1WITH duplicates AS (
 2  SELECT id, ROW_NUMBER() OVER(
 3    PARTITION BY firstname, lastname, email
 4    ORDER BY age DESC
 5  ) AS rownum
 6  FROM contacts
 7)
 8DELETE contacts
 9FROM contacts
10JOIN duplicates USING(id)
11WHERE duplicates.rownum > 1

Postgres

 1WITH duplicates AS (
 2  SELECT id, ROW_NUMBER() OVER(
 3    PARTITION BY firstname, lastname, email
 4    ORDER BY age DESC
 5  ) AS rownum
 6  FROM contacts
 7)
 8DELETE FROM contacts
 9USING duplicates
10WHERE contacts.id = duplicates.id AND duplicates.rownum > 1;

source

You might also like:

If this post was enjoyable or useful for you, please share it! If you have comments, questions, or feedback, you can email my personal email. To get new posts, subscribe use the RSS feed.