How do you delete duplicate rows in mysql without an id column?

by haylee.mertz , in category: MySQL , a month ago

How do you delete duplicate rows in mysql without an id column?

Facebook Twitter LinkedIn Telegram Whatsapp

1 answer

Member

by lizzie , a month ago

@haylee.mertz 

You can use the following query to delete duplicate rows in MySQL without an id column:

1
2
3
4
5
6
7
DELETE t1 FROM your_table t1
INNER JOIN your_table t2
WHERE t1.column1 = t2.column1
AND t1.column2 = t2.column2
AND t1.column3 = t2.column3
AND t1.columnN = t2.columnN
AND t1.primary_key > t2.primary_key


Replace your_table with the name of your table and column1, column2, column3, columnN with the columns that you want to check for duplicates. Replace primary_key with the primary key column of your table. This query will delete all duplicate rows except the one with the highest primary key value.