I have a table in MySQL named ZipCode and its fields are: id, zipcode, city, state. It has some duplicate records; some zipcode are showing two times I want to remove all that zipcodes that are coming twice; each zipcode must be stored just once. How can I make it correct?
Asked
Active
Viewed 333 times
3
-
possible duplicate of [Delete Duplicate SQL Records](http://stackoverflow.com/questions/841447/delete-duplicate-sql-records) – Bohemian Aug 21 '11 at 23:18
3 Answers
7
-- remove duplicates
DELETE z1
FROM ZipCode z1, ZipCode z2
WHERE z1.zipcode = z2.zipcode AND z2.id < z1.id;
-- add a primary key
ALTER TABLE ZipCode ADD PRIMARY KEY (zipcode);
-- or a unique key
ALTER TABLE ZipCode ADD UNIQUE INDEX zipcode_unique (zipcode);
Arnaud Le Blanc
- 98,321
- 23
- 206
- 194
4
This set of three queries will remove all duplicates:
CREATE TABLE new_table AS SELECT * FROM old_table WHERE 1 GROUP BY zip;
DROP TABLE old_table;
RENAME TABLE new_table TO old_table;
EdoDodo
- 8,220
- 3
- 24
- 30
-
2and don't forget to re-create indexes and constraints after that :) – Arnaud Le Blanc Aug 21 '11 at 19:05
-
... _and_ introduce a primary-key constraint on the new table to prevent recurrence, – hmakholm left over Monica Aug 21 '11 at 19:06
-
Yup, true. And also, fix the code that was inserting duplicates to have it check that the row it is inserting is unique. – EdoDodo Aug 21 '11 at 19:07
-
3You could change this to `CREATE TABLE new_table LIKE old_table; INSERT INTO new_table SELECT * FROM old_table WHERE 1 GROUP BY zip;` to avoid having to recreate indices and such. – Michael Mior Aug 21 '11 at 19:28
0
Try this its working fine
DELETE
FROM
insurance_policy_customers ipc
WHERE
ipc.id NOT IN (SELECT
min(ipc.id)
FROM
insurance_policy_customers ipc
GROUP BY
ipc.resident_insurance_policy_id )
alecxe
- 462,703
- 120
- 1,088
- 1,195
