CODEWITHSUNDEEP
mysql
hacking_mike
hacking_mike

Reputation: 1151

Delete duplicate rows in mySQL in same table

I have this script running to check for duplicates in my table:

select s.id, t.* 
from [stuff] s
join (
   select name, city, count(*) as qty
   from [stuff]
   group by name, city
   having count(*) > 1
) t on s.name = t.name and s.city = t.city

This works fine and returns the ID's of the duplicate rows:

myresult = cur.fetchall()
print(myresult)

Example output:

[(84,), (85,), (339,), (340,), (351,), (352,), (416,), (417,), (511,), (512,), (532,), (533,), 
(815,), (816,), (978,), (979,), (1075,), (1076,), (1385,), (1386,), (1512,)]

Now I want to delete records 84, 339, 351, 416, etc. What would be the most convenient way to do so?

Upvotes: 0

Views: 308

Answers (2)

Christian Bongiovanni
Christian Bongiovanni

Reputation: 1

you can remove duplicate rows in MySQL in this way

      WHERE customer_id NOT IN
      (
          SELECT
          customer_id
          FROM
          (
              SELECT MIN(customer_id) as customer_id
              FROM CUSTOMERS
              GROUP BY CONCAT(first_name, last_name, phone)
          ) AS duplicate_customer_ids
      );`

Upvotes: 0

Sabil
Sabil

Reputation: 4520

MySQL provides you with the DELETE JOIN statement that allows you to remove duplicate rows quickly.

The following statement deletes duplicate rows and keeps the highest id:

DELETE t1 FROM table_name t1
INNER JOIN table_name t2 
WHERE 
    t1.id < t2.id AND 
    t1.unique_col = t2.unique_col;

In case you want to delete duplicate rows and keep the lowest id, you can use the following statement:

DELETE t1 FROM table_name t1
INNER JOIN table_name t2 
WHERE
    t1.id > t2.id AND 
    t1.unique_col = t2.unique_col;

Upvotes: 1

Related Questions