'Delete with "Join" in Oracle sql Query

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


Solution 1:[1]

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.

Solution 2:[2]

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

Solution 3:[3]

Personally, I would use the EXISTS construct. As described in the examples on this web page:

DELETE ProductFilters pf
WHERE EXISTS (
   SELECT *
   FROM Products p
   WHERE p."productID"=pf."productID"
   AND p.NAME= 'Mark'
)
AND pf."id">=200;

Solution 4:[4]

Please use a subquery

delete from productfilters
where productid in (Select id from products where name='Mark') and Id>200;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Radu Gheorghiu
Solution 2 Gregory Bologna
Solution 3 Nicolaesse
Solution 4 MertG

Related Questions