Suresh Yadav

Description : I want delete duplicate zipcode from zipcode table, in that table i have zipcode field, which one have duplicate zipcods. After searching and help of Suresh we have got the solution to delete duplicate records, but in that table u have need one filed as primary key. In this query id is working as primary key. See b below for the that terriffic query :)

delete bad_rows.*
from new_zipcodes as bad_rows
inner join (
select zipcode, MIN(id) as min_id
from new_zipcodes
group by zipcode
having count(*) > 1
) as good_rows on good_rows.zipcode = bad_rows.zipcode
and good_rows.min_id <> bad_rows.id;