Jeff H. Jeff H. - 4 months ago 20
SQL Question

MYSQL DELETE row if duplicate key exists in same table with specified value

This may be tagged as a duplicate, but I wasn't able to find this example in my search.

I have a product-to-category table that allows duplicate keys, therefore allowing products to be placed in multiple categories by ID. I have a situation where several thousand products were incorrectly placed in a category. If the product is assigned to 'Action Figures' and 'Comics', then the 'Comics' assignation is erroneous.

I'm attempting to write a single MYSQL query that will delete rows if a row with a duplicate key and the specified value for 'Action Figures' is detected. Another way to say it: If a product is assigned to both categories, then I want to delete the row assigning it to the erroneous category.

I can't seem to figure out a delete statement that can search within the same table by ID the way I want.

Here's what I've got so far (EDIT- Thanks to the answer below):

DELETE FROM oc_product_to_category c1
WHERE c1.category_id = '25'
SELECT * FROM oc_product_to_category c2
WHERE c1.product_id = c2.product_id AND c2.category_id = '24')

Unfortunately, this is producting a syntax error.

You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near
WHERE c1.category_id = '25'
SELECT * FROM oc_product_to_catego' at line 1

When I try the syntax

DELETE c1 FROM oc_product_to_category c1 ...

I get an error:

You can't specify target table 'c1' for update in FROM clause


This should get you started. If an ID is assigned to both category_id 25 and 33, it will delete the record(s) with category 25.

DELETE c1 FROM oc_product_to_category as c1
WHERE c1.category_id = '25'
    SELECT id FROM (SELECT * FROM oc_product_to_category) as c2 
    WHERE = AND c2.category_id = '33'

Note that this won't remove duplicate records with category_id 33 if any exist.