How to delete duplicate records in MySQL, in a table without IDs? 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 &lt; p2.id AND p1.product = p2.product;
There is no any field combination which identifies the record uniqually.

I see at least 2 different solutions.

First solution: move unique records to a copy of table and replace original table.

CREATE TABLE temp LIKE products;
INSERT INTO temp 
    SELECT DISTINCT * FROM products;
DROP TABLE products;
RENAME TABLE temp TO products;


Second solution: add temporary autoincrement, delete records using it, and drop temp field.

ALTER TABLE products ADD COLUMN temp SERIAL PRIMARY KEY;
DELETE t1.* 
    FROM products t1 
    LEFT JOIN ( SELECT MIN(temp) mintemp 
                FROM products
                GROUP BY field1,field2 /* , ... */ , fieldN) t2 
        ON t1.temp=t2.mintemp 
    WHERE t2.mintemp IS NULL;
ALTER TABLE products DROP COLUMN temp;






UPDATE 

In second variant: the additional column definition as a primary key is redundant. It is enough to use

ALTER TABLE products ADD COLUMN temp SERIAL;