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' );
Advertisement
Answer
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' ) );