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'
);
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.