How to Delete Duplicate Rows from a Table Without Creating a New Table?

Deleting Duplicate Rows from a Table

You have a table with duplicate records, and you want to delete them without creating a new table. Assuming you have a unique ID field, you can use the following query:

DELETE FROM Table
WHERE ID NOT IN
(
SELECT MIN(ID)
FROM Table
GROUP BY Field1, Field2, Field3, ...
)

Example: Let's say you have the following table:

id	action	L1_name	L1_data	...	L11_data
1	action1	name1	data1	...	company1
2	action2	name2	data2	...	company2
3	action1	name1	data1	...	company1

The query would delete the duplicate record with id 3, leaving you with the following table:

id	action	L1_name	L1_data	...	L11_data
1	action1	name1	data1	...	company1
2	action2	name2	data2	...	company2

Notes:

• "Table" and "ID" are representative names.
• The list of fields ("Field1, Field2, ...") should include all fields except for the ID.
• This query may be slow depending on the number of fields and rows.
• If you don't have a unique index, add one to improve performance.

The above is the detailed content of How to Delete Duplicate Rows from a Table Without Creating a New Table?. For more information, please follow other related articles on the PHP Chinese website!