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>=200 
And pf.rowid in 
(
     Select rowid from PRODUCTFILTERS 
     inner join PRODUCTS on PRODUCTFILTERS.PRODUCTID = PRODUCTS.ID 
     And PRODUCTS.NAME= 'Mark'
);

yes, you can delete using a join: see here stackoverflow.com/a/3675205/110933 — davek
– davek, Commented Oct 1, 2012 at 11:09

Radu Gheorghiu · Accepted Answer · 2015-10-21 14:58:05Z

122

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.

edited Oct 21, 2015 at 14:58

Radu Gheorghiu

20.6k16 gold badges78 silver badges113 bronze badges

answered Oct 21, 2015 at 14:55

xlogic

1,5692 gold badges11 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Ivan Gerasimenko Over a year ago

does this query delete rows only from the first table 'productfilters' or from both of the tables?

Kevin Ortman Over a year ago

@IvanGerasimenko it deletes rows from both tables.

yohannc Over a year ago

@KevinOrtman If there is a cascade delete action on FK constraint then yes, otherwise no. (look after the explain plan to see wich one, cascade won't be showed)

chillworld Over a year ago

if you do select pf.* it will only delete the productfilters?

NTR Over a year ago

@chillworld Yes. I tested it. Also, this should be the accepted answer. Far superior to the current.

|

davek · Accepted Answer · 2012-10-01 11:37:07Z

61

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

edited Oct 1, 2012 at 11:37

answered Oct 1, 2012 at 11:12

davek

23k11 gold badges80 silver badges97 bronze badges

2 Comments

arjacsoh Over a year ago

I have tried the second one and I receive the error: "Column ambiguously defined". Besides I think that the correct syntax at the begin is delete PRODUCTFILTERS where... A "From" is superfluous in a delete Query.

Gregory Bologna Over a year ago

rowid is ambiguous without its alias. For a delete with join, the query is fine.

Gregory Bologna · Accepted Answer · 2021-01-22 17:17:24Z

5

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

answered Jan 22, 2021 at 17:17

Gregory Bologna

2841 gold badge9 silver badges20 bronze badges

Comments

Nicolaesse · Accepted Answer · 2022-02-01 21:11:06Z

4

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;

answered Feb 1, 2022 at 21:11

Nicolaesse

2,76413 gold badges51 silver badges78 bronze badges

Comments

Geonarf · Accepted Answer · 2023-12-18 17:51:33Z

1

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

answered Dec 18, 2023 at 17:51

Geonarf

413 bronze badges

Comments

Chris Saxon · Accepted Answer · 2024-05-24 09:48:31Z

0

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'

answered May 24, 2024 at 9:48

Chris Saxon

10.2k1 gold badge33 silver badges47 bronze badges

Comments

MertG · Accepted Answer · 2021-06-21 01:02:51Z

-1

Please use a subquery

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

edited Jun 21, 2021 at 1:02

MertG

7631 gold badge7 silver badges25 bronze badges

answered Jun 18, 2021 at 17:05

maniram reddy

71 gold badge1 silver badge8 bronze badges

Collectives™ on Stack Overflow

Delete with "Join" in Oracle sql Query

7 Answers 7

11 Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

11 Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related