Delete with "Join" in Oracle sql Query
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'
)
);
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.