Remove duplicate record from table

Question

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?

Answer 1

-- 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);