Delete duplicate rows in mysql

Question

I have a location mysql db contain 3 columns

Country     State           City
US          California      Los Angeles
AU          Queensland      Brisbane
AU          Victoria        Southbank
AU          Queensland      Brisbane

How can I delete duplicate rows in mysql query?

DELETE FROM location WHERE country IN (
  SELECT * FROM table_name 
  GROUP BY country, state, city 
  HAVING COUNT(*) >
);

I tried this query, but it didn't work

Answer 1

Select distinct rows and put them in a new table:

CREATE TEMPORARY TABLE location_temp AS SELECT DISTINCT * FROM location

Truncate table:

DELETE FROM location;

Populate from the table with distinct values

INSERT INTO location(Country, State, City) SELECT Country, State, City FROM location_temp;

Resource: http://www.databasejournal.com/features/mysql/article.php/2201621/Deleting-Duplicate-Rows-in-a-MySQL-Database.htm

Answer 2

Your query has numerous issues:

DELETE FROM location
where country in (SELECT *
                  FROM table_name 
                  GROUP BY country, state, city 
                  having COUNT(*)>);

Such as: (1) you are returning more than one column from the subquery but comparing to only one column. (2) You have no number after the comparison. (3) It refers to two different tables.

The way your data is structured (with no unique id on a row), it is challenging to determine which rows to delete. I would suggest that you use the truncate method instead. And then learn to put an auto incrementing id in every table:

create temporary table tmpLocation as
    select country, state, city from location;

truncate table location;

insert into location(country, state, city)
    select distinct country, state, city
    from tmpLocation;

drop table tmpLocation;

On large tables with large numbers of deletes, this is more efficient as well.

Answer 3

You have to delete duplicate rows by using ALTER query

ALTER IGNORE TABLE location ADD UNIQUE INDEX index_name(Country, State, City);

Or otherwise you can remove duplicate rows by using below Query Syntax

DELETE
FROM table_name
WHERE col1 IN  (  
    SELECT MAX(col1)
    FROM tab_name
    GROUP BY col1, col2, col3, ....
    HAVING COUNT(*) > 1)

Answer 4

You forgot to mention count(*)>1

DELETE FROM location
where country in (
SELECT *
FROM table_name 
GROUP BY country, state, city 
having COUNT(*)>1);

Just for your information. You can also use ALTER IGNORE TABLE to delete the duplicates.

Something like this:-

ALTER IGNORE TABLE location ADD UNIQUE INDEX (COLUMN1, COLUMN2)