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;
  • ROW_NUMBER is there PostgreSQL, is there some function like that in mysql – Edwin Babu Aug 15 at 5:11
  • By "duplicate", you mean that all columns have the same values? – Rick James Aug 26 at 19:08
  • yes, all columns having same value @RickJames – Edwin Babu Aug 27 at 2:15
up vote 8 down vote accepted

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;
  • You could also use a hash function to create a semi-unique ID, and then delete duplicate hash values. – Russell Fox Aug 15 at 14:46
  • In your first suggestion, do it this way so that the table is never missing: RENAME TABLE products TO old, temp TO products; DROP TABLE old; – Rick James Aug 26 at 19:09

Apart from Akinas answer, You could delete both rows and then insert one.

You should also really, really add a primary key to your table even if you don't need to for performance, specifically to avoid situaitons like this.

You could do a

Delete <condition> limit 1

That will only delete 1 row, even if multiple rows matches the condition. This is explained in the official manual:

Order of Deletion

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;
  • 1
    He wants to delete all but one of the duplicates, not just one. And how would you do this across all product products? – Barmar Aug 15 at 17:53

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.