In MySQL, I have a table named "city" with "city_name" and "latitude" and "longitude" in it. It appears that I have a few duplicates. What query can I run (in phpmyadmin) that will search through the "city" table for entries containing the same latitude and longitude (with different "city_name"'s)?


Run the query in mysql directly. First access mysql from command line:

sudo mysql -p

Then select the database you want to use

show databases;
use databaseiwant;

Now run a query on the table you need to find duplicates in

show tables;
SELECT fieldtocheckduplicates, COUNT(fieldtocheckduplicates) AS NumOccurrences FROM tabletocheck GROUP BY fieldtocheckduplicates HAVING ( COUNT(fieldtocheckduplicates) > 1 );
  • As long as you're not expecting more than a few dozen duplicates, this should work fine. You could write a similar query to delete the duplicates as well. – Calvin Froedge Jan 30 '11 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.