Delete with "Join" in Oracle sql Query

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.


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

Use a subquery in the where clause. For a delete query requirig a join, this example will delete rows that are unmatched in the joined table "docx_document" and that have a create date > 120 days in the "docs_documents" table.

delete from docs_documents d
where d.id in (
    select a.id from docs_documents a
    left join docx_document b on b.id = a.document_id
    where b.id is null
        and floor(sysdate - a.create_date) > 120
 );