CODEWITHSUNDEEP
mysql
LoganFrederick
LoganFrederick

Reputation: 327

MySQL: Selecting One Record When Others Have Same Data

I have a table of cities that all share the same area code:

367 01451   Harvard         Worcester   Massachusetts   MA  978 Eastern
368 01452   Hubbardston Worcester   Massachusetts   MA  978 Eastern
369 01453   Leominster  Worcester   Massachusetts   MA  978 Eastern

The table has multiple area codes, all with multiple cities.

What I'd like to do is only select one city from each area code and delete any extra cities from duplicate area codes. What would be the best query to accomplish this?

I believe: Mysql4: SQL for selecting one or zero record

Is coming close to what I need but didn't quite get what/how those answers were working.

Note The "978" row is the "area_code" row, table name is "zip_code".

Upvotes: 0

Views: 167

Answers (1)

Quassnoi
Quassnoi

Reputation: 425301

DELETE  c.*
FROM    zip_code c
JOIN    (
        SELECT  area_code, MIN(id) AS mid
        FROM    zip_code
        GROUP BY
                area_code
        ) co
ON      c.area_code = co.area_code
        AND c.id <> co.mid

Upvotes: 1

Related Questions