How to remove duplicate records in MySQL with WHERE condition

Question

How to remove duplicate records in MySQL with WHERE condition

Navigation

#1 by (0 votes)

3

My address book table has many duplicate addresses for each client. Note in the screenshot below that I have many duplicate zip codes for the same client with ID 12.

Ineedtoremoveduplicateaddresseswherepostcodeandparent_idaregreaterthan1andkeepanaddressinthesamecondition,ieyouhavetomaintaina14402-408,14403-707,60150-170,and81050-130.

Inasimplewayyouwouldneedthefollowing:

DELETE*FROMcatalog_addressWHEREparent_idANDpostcode>1

Icouldnotfindsimilarcase,Isawenoughissuesofremovingduplicatesasbelow: How to delete all duplicates, except one? My seeing does not solve my case.

What would the MYSQL statement look like in this case?

Thank you!

mysql delete

asked by anonymous 24.09.2018 / 14:40

1 answer

How to map two LIST properties using Automapper How to get data from a table in a website?

score 0 · Accepted Answer

You can make a INNER JOIN with the same table to find the duplicates. In your case it will be considered a duplicate if there is another record with the same parent_id , the same post_code and a entity_id minor:

DELETE b
FROM 'catalog_address' a
INNER JOIN 'catalog_address' b ON a.'parent_id' = b.'parent_id' AND a.'post_code' = b.'post_code' AND a.'entity_id' < b.'entity_id'
WHERE a.'parent_id' > 1 AND a.'post_code' > 1;

To query the records that will be removed in DELETE above, use SELECT below:

SELECT b.*
FROM 'catalog_address' a
INNER JOIN 'catalog_address' b ON a.'parent_id' = b.'parent_id' AND a.'post_code' = b.'post_code' AND a.'entity_id' < b.'entity_id'
WHERE a.'parent_id' > 1 AND a.'post_code' > 1;