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

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

Your SBI Debit card ending with XX0000 is deactivated only for Internet txn.

SBI account holders may have received an SMS with following message, supposed to be from State Bank of India (SBI).

Your SBI Debit card ending with XX0000  is deactivated only for Internet txn. To activate send SMS "SWON ECOM 0000" to 09223966666. No change for ATM/POS usage
** Replace the four Zeros with last 4 digits of your debit card number

Recently many of the SBI account holder has losed their money due to a hi-tech ATM robbery which happened in Thiruvananthapuram, capital city of Kerala.

How to turn off BSNL Buzz services - Steps to deactivate BSNL Buzz messages

BSNL Buzz is a service from BSNL for mobile customers, in which when your phone is in idle mode, teasers or small buzz messages related to topics like entertainment, lifestyle, sports, business, fun, polls, daily news updates are delivered to your mobile. When you activate a BSNL SIM card, through which BSNL BUZZ is available, you automatically accept the terms and conditions for teaser delivery.

Recently, I bought a new BSNL SIM card. After the activation, when ever I unlock the phone, I was getting lots of buzz message one after another. I have to click on cancel / back button multiple time to view the home screen. Some times when some call arrives, it was difficult to attend calls due to this teaser popups. So I decided to deactivate the BSNL buzz. Here are the steps to Deactivate or Turn Off BSNL Buzz.


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