snippetsqlMajor
How to delete duplicate records in MySQL, in a table without IDs?
Viewed 0 times
withoutdeleteduplicaterecordsidsmysqlhowtable
Problem
I need to delete the duplicate records in this table. However, there is no
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 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.
Second solution: add temporary autoincrement, delete records using it, and drop temp field.
UPDATE
In second variant: the additional column definition as a primary key is redundant. It is enough to use
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.