How to remove duplicate records in Mysql with WHERE condition

Asked

Viewed 1,791 times

3

My address book table has many duplicated addresses for each client. Note in the image below that I have many duplicate Zip Codes for the same client with ID 12.

inserir a descrição da imagem aqui

I need to remove duplicate addresses where postcode and parent_id are greater than 1 and maintain an address in the same condition, ie have to maintain a 14402-408, 14403-707, 60150-170 and 81050-130.

In a simple way I would need the following:

DELETE * FROM catalog_address
WHERE parent_id AND postcode > 1

I could not find similar case, I saw enough questions to remove duplicates as below: How to delete all duplicates except one?, but in my opinion it doesn’t solve my case.

How would the MYSQL instruction for this case?

Thank you!

mysql delete
  • 1

    Possible duplicate of How to delete all duplicates except one?

  • Sorry @Sorack, my forte is not MYSQL, but it seems that the possible answer attached does not answer my question, because I have to test if condition A and B are greater than 1 to delete.

  • I don’t have MYSQL here to check, but you can test something like this: delete from catalog_address Where postcode = '14402-408' and rowid not in (select min(rowid) from catalog_address Where postcode = '14402-408' )

  • @Diegoqueiroz, do you have an id field in this table? Because without it it will not be possible to remove duplicates.

  • I have an auto increment field named entity_id @Robertodecampos.

1 answer

0


You can make a INNER JOIN with the same table to find duplicates. In your case it will be considered duplicated 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 view the records that will be removed on DELETE above, use the SELECT down 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;

  • That’s what I needed @Roberto de Campos. :)

Browser other questions tagged mysql delete

You are not signed in. Login or sign up in order to post.