Skip to main content

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:

Popular posts from this blog

How to apply for a new ration card and what are the documents required?

List of documents required for new ration card application in Kerala and how to apply


Application for a new ration card should be addressed to Taluk Supply Officer (TSO) / City Rationing Officer (CRO) of applicant's residing area.

Primary document required are Residence certificate, Income certificate and incase the applicant's name is in another ration card then he/she should submit the reduction certificate ontained from previous TSO / CRO as proof for removing his/her name from old Ration card.

You can get the new application from your currently residing Taluk Supply Office.

Payback Points - How to redeem - How to merge multiple payback accounts - Block Payback card - Payback customer care

Joomla and Forum Integration - Integrating Forums to Joomla

Joomla is one of the most popular CMS opensource packages. It is very easier to develop website's using Joomla. You just need to download Joomla package from Joomla's Official website www.joomla.org  and install it on your domain and later adding customizations to templates and feature and Your website is ready :). Now a days most websites provides a forum section for it users for discussing various article topics, gathering opinions etc.



Following are some best know forum opensource packages which can be integrated with Joomla and create a new forum experience for users


Urgent Openings for PHP trainees, Andriod / IOS developers and PHP developers in Kochi Trivandrum Calicut and Bangalore. Please Send Your updated resumes to recruit.vo@gmail.com   Read more »
Member
Search This Blog