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 gt  200  And pf rowid in         Select rowid from PRODUCTFILTERS       inner join PRODUCTS on PRODUCTFILTERS PRODUCTID   PRODUCTS ID       And PRODUCTS NAME   Mark

User · Answer

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.

User · Answer

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  quot docx document quot  and that have a create date  gt  120 days in the  quot docs documents quot  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   gt  120

User · 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 gt  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 gt  200  And pf rowid in           Select PRODUCTFILTERS rowid from PRODUCTFILTERS       inner join PRODUCTS on PRODUCTFILTERS PRODUCTID   PRODUCTS ID       And PRODUCTS NAME   Mark

[sql] Delete with "Join" in Oracle sql Query

Examples related to sql

Examples related to oracle

Examples related to sql-delete