HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMajor

How to delete duplicate records in MySQL, in a table without IDs?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
withoutdeleteduplicaterecordsidsmysqlhowtable

Problem

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.id AND p1.product = p2.product;

Solution

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;

Code Snippets

CREATE TABLE temp LIKE products;
INSERT INTO temp 
    SELECT DISTINCT * FROM products;
DROP TABLE products;
RENAME TABLE temp TO products;
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;
ALTER TABLE products ADD COLUMN temp SERIAL;

Context

StackExchange Database Administrators Q#214946, answer score: 20

Revisions (0)

No revisions yet.