SQL How to Delete Data from Table Using INNER JOIN

Question

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)".

write a sub query to get all records and then use where clause. DELETE oc_product FROM oc_product where oc_product in (sub query ) — cherry
– cherry, Commented Dec 11, 2017 at 22:15
@sreenivas I tried it but the error "Table 'oc_product' is specified twice, both as a target for 'DELETE' and as a separate source for data" comes up. I understand the problem but what do I need to change? — Gilbert
– Gilbert, Commented Dec 11, 2017 at 23:55

clinomaniac · Accepted Answer · 2017-12-11 22:27:50Z

1

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

answered Dec 11, 2017 at 22:27

clinomaniac

2,2182 gold badges19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gilbert Over a year ago

It works perfectly. Could you perhaps explain what the " .* " does exactly, and what the "p" and "pc" stand for? Thanks a lot for the help.

clinomaniac Over a year ago

p and pc are aliases so that you don't have to use the full table name every time. p.* just says that delete rows from p and not from pc.

Collectives™ on Stack Overflow

SQL How to Delete Data from Table Using INNER JOIN

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related