James Tookey

Full stack developer based in the Cotswolds, UK

GitHub profile
StackOverflow Profile
Twitter

Latest blogs

MySQL: Delete duplicates
19 Mar 2018 at 13:43
Imagick: Automatic line wrapping
28 Feb 2018 at 07:48
MySQL: Selecting one random item from each category
27 Feb 2018 at 15:57
19 March 2018

MySQL: Delete duplicates

by James Tookey

I needed to add a unique key to a column on an existing table. When I tried to do so, it failed because the values in that column were not unique. After looking at the data, I could simply remove any duplicates before adding my unique key.

The query to run is below:

DELETE t FROM table AS t
LEFT JOIN (
    SELECT MAX(updated_at) AS max_updated_at, name
    FROM table
    GROUP BY name
) AS tmp
ON t.updated_at = tmp.max_updated_at
AND t.name = tmp.name
WHERE tmp.max_updated_at IS NULL

This query works by retrieving the last updated version of each row that needs to be unique. This is because we will only keep the most recently updated rows. The left join means that all rows will be analysed for deletion and the WHERE clause ensures we only delete the rows that do not match the most recently updated ones.

tags: mysql