HOW TO DELETE DUPLICATE RECORDS FROM A MYSQL TABLE BY KEEPING ONLY ONE RECORD WITH HIGHEST OR LOWEST ID VALUE

HOW TO DELETE DUPLICATE RECORDS FROM A MYSQL TABLE BY KEEPING ONLY RECORD WITH HIGHEST OR LOWEST ID VALUE

Deleting duplicate records from a mysql table by keeping only record with highest or lowest id value.

Here I am explaining this with an example table. Let the table name be "tbl_films" . The table has fields "fId" and "fName"


tbl_films
fId | fName
------------
1 | Film1
2 | Film2
3 | Film1
4 | Film1
5 | Film3



see here the "Film1" is repeating

the records are
SELECT * FROM tbl_films WHERE fName="Film1"

fId | fName
------------
1 | Film1
3 | Film1
4 | Film1



DELETING DUPLICATES BY KEEPING THE LOWEST ID VALUE

If you want to keep the record with the lowest fId value ie, 1:

DELETE FROM tbl_films
WHERE fId NOT IN
(SELECT * FROM
(SELECT MIN(F.fId) FROM tbl_films F GROUP BY F.fName)
)

Query explanation starting from most inner query:

Level2 : SELECT MIN(F.fId) FROM tbl_films F GROUP BY F.fName will return all lower fId values and values will be 1,2 and 5

Level1 : SELECT * FROM (SELECT MIN(F.fId) FROM tbl_films F GROUP BY F.fName) will return records with fId 1,2 and 5

Level 0: DELETE FROM tbl_films WHERE fId NOT IN (SELECT * FROM (SELECT MIN(F.fId) FROM tbl_films F GROUP BY F.fName) ) will delete all the records except the records with lower fId 1,2 and 5

result will be:
tbl_films
fId | fName
------------
1 | Film1
2 | Film2
5 | Film3




DELETING DUPLICATES BY KEEPING THE HIGHEST ID VALUE

If you want to keep the record with highest fId value ie,4 :

DELETE FROM tbl_films
WHERE fId NOT IN
(SELECT * FROM
(SELECT MAX(F.fId) FROM tbl_films F GROUP BY n.name)
)

Query explanation starting from most inner query:

Level2 : SELECT MAX(F.fId) FROM tbl_films F GROUP BY F.fName will return all higher fId values and values will be 2,4 and 5

Level1 : SELECT * FROM (SELECT MAX(F.fId) FROM tbl_films F GROUP BY F.fName) will return records with fId 2,4 and 5

Level 0: DELETE FROM tbl_films WHERE fId NOT IN (SELECT * FROM (SELECT MAX(F.fId) FROM tbl_films F GROUP BY F.fName) ) will delete all the records except the records with higher fId 2,4 and 5

result will be

tbl_films
fId | fName
------------
2 | Film2
4 | Film1
5 | Film3

MySQL Error : #1248 - Every derived table must have its own alias
If you get a MySQL error like
"#1248 - Every derived table must have its own alias"

Every derived table (sub-query or inner query) must  have an alias. Inother words each query in brackets  ( sub query ) must be given an alias (AS AliasName), which can the be used to refer to it in the rest of the outer query.

So in such case Try this one:

To keep only latest inserted record:

DELETE FROM tbl_films
WHERE fId NOT IN
(SELECT * FROM
(SELECT MAX(F.fId) FROM tbl_films F GROUP BY n.name) as InnerQry
)

To keep on oldest inserted record:

DELETE FROM tbl_films
WHERE fId NOT IN
(SELECT * FROM
(SELECT MIN(F.fId) FROM tbl_films F GROUP BY n.name) as InnerQry
)


Read How to retrieve only one record for a repeating column or rows of data from a mysql table.

Related posts:

• How to find duplicate records in a table. - MySQL or SQL Query to find duplicates in a table
• How to delete duplicate records from a mysql table by keeping only one record with highest or lowest id value
• MySQL Query for getting subcategory name and parent category name in a single row
• MySQL Query for getting nested items in a single query
• For MySQL related articles, Click here