Delete with "Join" in Oracle sql Query

Question

I am not deeply acquainted with Oracle Sql Queries, therefore I face a problem on deleting some rows from a table which must fulfill a constraint which includes fields of another (joining) table. In other words I want to write a query to delete rows including JOIN.

In my case I have a table ProductFilters and another table Products joined on fields ProductFilters.productID = Products.ID. I want to delete the rows from ProductFilters having an ID higher or equal to 200 and the product they refer has the name 'Mark' (name is a field in Product).

I would like to be informed initially if JOIN is acceptable in a Delete Query in Oracle. If not how should I modify this Query in order to make it work, since on that form I receive an error:

DELETE From PRODUCTFILTERS pf  where pf.id>=200  And pf.rowid in  (      Select rowid from PRODUCTFILTERS       inner join PRODUCTS on PRODUCTFILTERS.PRODUCTID = PRODUCTS.ID       And PRODUCTS.NAME= 'Mark' );        

Answer 1

Recently I learned of the following syntax:

DELETE (SELECT *         FROM productfilters pf         INNER JOIN product pr             ON pf.productid = pr.id         WHERE pf.id >= 200             AND pr.NAME = 'MARK') 

I think it looks much cleaner then other proposed code.

Answer 2

Based on the answer I linked to in my comment above, this should work:

delete from ( select pf.* From PRODUCTFILTERS pf  where pf.id>=200  And pf.rowid in    (      Select rowid from PRODUCTFILTERS       inner join PRODUCTS on PRODUCTFILTERS.PRODUCTID = PRODUCTS.ID       And PRODUCTS.NAME= 'Mark'   ) );  

or

delete from PRODUCTFILTERS where rowid in ( select pf.rowid From PRODUCTFILTERS pf  where pf.id>=200  And pf.rowid in    (      Select PRODUCTFILTERS.rowid from PRODUCTFILTERS       inner join PRODUCTS on PRODUCTFILTERS.PRODUCTID = PRODUCTS.ID       And PRODUCTS.NAME= 'Mark'   ) );