CODEWITHSUNDEEP
mysqlphpmyadmin
Gilbert
Gilbert

Reputation: 83

SQL How to Delete Data from Table Using INNER JOIN

I'm trying to delete all records form the table "oc_products" that don't have a certain category ID. I created a SELECT query that lists those products using an INNER JOIN, since the categories are in a separate table.

What I can't figure out is how to use the DELETE function to delete the shown records.

This is what my code looks like:

DELETE oc_product
FROM oc_product
INNER JOIN oc_product_to_category ON oc_product.product_id = oc_product_to_category.product_id
WHERE oc_product_to_category.category_id = 343

Its showing the error "Unexpected keyword, (near INNER JOIN)".

Upvotes: 0

Views: 1707

Answers (1)

clinomaniac
clinomaniac

Reputation: 2218

Add .* to p in your first line.

Try:

DELETE p.* FROM oc_product p
INNER JOIN oc_product_to_category pc ON p.product_id = 
    pc.product_id
WHERE pc.category_id = 343

Upvotes: 1

Related Questions