CODEWITHSUNDEEP
sqloracle-databasesql-delete
arjacsoh
arjacsoh

Reputation: 9262

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

Upvotes: 69

Views: 203736

Answers (7)

Chris Saxon
Chris Saxon

Reputation: 9875

Oracle Database 23ai added direct joins for delete. So you can do:

delete productfilters pf 
from   products p
where  pf.id >= 200 
and    pf.productid = p.id 
and    p.name= 'Mark'

Upvotes: 0

Geonarf
Geonarf

Reputation: 41

I think better way are to use EXISTS

DELETE FROM Table1 t1
WHERE SOME_ID = 12345
AND EXISTS (SELECT 1 
            FROM Table2 t2
            WHERE t2.ST_ID = t1.ST_ID
            AND SOME_EVENT_ID = 404
            )
;

Upvotes: 1

Nicolaesse
Nicolaesse

Reputation: 2724

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;

Upvotes: 4

maniram reddy
maniram reddy

Reputation: 7

Please use a subquery

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

Upvotes: -1

Gregory Bologna
Gregory Bologna

Reputation: 284

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

Upvotes: 5

xlogic
xlogic

Reputation: 1559

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.

Upvotes: 121

davek
davek

Reputation: 22925

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

Upvotes: 59

Related Questions