CODEWITHSUNDEEP
mysqlsql
sabiv
sabiv

Reputation: 103

Deleting duplicates from listed records with SQL in PhpMyAdmin

I'm using this command to find records in my database that has the same value in the city column:

SELECT city, COUNT(*) c FROM allCity GROUP BY city HAVING c > 1;

When I run this in PhpMyAdmin I get a huge list with two columns, the first column is the city and the second called c. I assume the numbers in the c column shows the number of the duplicated entries, but I can't really list the duplicates, however maybe it doesn't needed.

So my goal is to delete all the duplicates from the list that I get after running the above command. Unfortunately that's the first time I'm using sql commands, therefore I would really appreciate if somebody could show me how should I extend my existing code or what should I implement to be able to do that? Basically I just want to remove every record where the city column has the same value, except the first record. For example if I have 3 records where the value is New York in the city column, I would like to delete two and keep one. It doesn't matter which two will be deleted.

Upvotes: 0

Views: 89

Answers (2)

Kontsnor
Kontsnor

Reputation: 146

It all depends on how the data is stored, and how big your database etc.

I usually use a query somewhat like this:

DELETE dup FROM allCity AS original 
LEFT JOIN allCity AS dup 
ON dup.city = original.city AND 
dup.objId != original.objId;

This statements selects all cities, joins the same table, but only if the Primary ID is not the same. In this case the WHERE clause isn't even needed, as we say that we only delete the duplicates (so where the ID is not the same as the original record).

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

This delete a city if exist other city with same name but smaller id. So at the end only the small id will remain in db.

DELETE FROM allCity AS c
WHERE exists (select tid 
              from allCity c2
              where c2.name = c.name
                and c2.id < c.id);

Upvotes: 1

Related Questions