Deleting duplicates in SQL

Question

I have a table with 3 columns - product_name, product_id and barcode. All rows have different names, id's but some rows have a duplicated barcode, I have the code I need to find the duplicates by counting them, but I can't figure out how to delete them.

I'm using MySQL workbench.

SELECT product_name, product_id, barcode, COUNT(barcode) FROM products.a GROUP BY barcode HAVING COUNT(barcode) > 1;

The duplicated barcodes should be deleted.

that is not how you should write/use [GROUP BY](https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html) in general.. -> *"SQL-92 and earlier does not permit queries for which the select list to nonaggregated columns that are not named in the GROUP BY clause"* .. in this case the nonaggregated columns are product_name and product_id — Raymond Nijland, Aug 01 '19 at 21:23
This answer may be useful https://stackoverflow.com/questions/6107167/mysql-delete-duplicate-records-but-keep-latest — lopushen, Aug 01 '19 at 21:27
Do you mean that you want the rows deleted or update just the barcode columns to null? — forpas, Aug 01 '19 at 21:46

score 0 · Answer 1 · answered Aug 01 '19 at 21:36

You can use join with delete. For instance, to delete all but the maximum id:

delete p
    from products p join
         (select barcode, max(product_id) as max_product_id
          from products p
          group by barcode
         ) pp
         on pp.barcode = p.barcode and pp.product_id < pp.max_product_id;

Deleting duplicates in SQL

1 Answers1