How to mysql delete duplicate rows in a table without id?
I need to delete the duplicate records in this table. However, there is no id for each row.
----------------------
Example Data
product amount quantity
table 2000 5
chair 300 25
TV 30000 4
bike 300 25
table 2000 5
chair 300 25
chair 300 25
Expected Results
I need to get this result.
product amount quantity
table 2000 5
chair 300 25
TV 30000 4
bike 300 25
Script with ID
If there were an id, I could have used:
DELETE p1 FROM products p1
INNER JOIN products p2
WHERE p1.id < p2 xss=removed>
13.2.2 DELETE Syntax
Order of Deletion
For mysql delete duplicate rows, you could do a
Delete limit 1That will only delete 1 row, even if multiple rows matches the condition. This is explained in the official manual:
If the DELETE statement includes an ORDER BY clause, rows are deleted in the order specified by the clause. This is useful primarily in conjunction with LIMIT. For example, the following statement finds rows matching the WHERE clause, sorts them by timestamp_column, and deletes the first (oldest) one:
DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;