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 http://stackoverflow.com/a/3675205/110933 — davek, Oct 01 '12 at 11:09

score 116 · Answer 1 · edited Oct 21 '15 at 14:58

116

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 '15 at 14:58

Radu Gheorghiu

20,049
16
72
107

answered Oct 21 '15 at 14:55

xlogic

1,509
2
11
9

Very clean indeed. Using this going forward so others have easier time understanding what exactly is being deleted. – user2025696 Mar 29 '16 at 15:14
1

This one is **much** more faster than the alternatives which mostly went through a where condition rather than a join. – Olivier Grégoire Apr 07 '16 at 13:05
1

Just wanted to add that, if the JOIN is with a collection (table of something), an error raises because no DML can be run on a PL/SQL collection. Nice syntax, though. – Alfabravo Oct 04 '16 at 20:26
10

does this query delete rows only from the first table 'productfilters' or from both of the tables? – Ivan Gerasimenko Mar 13 '17 at 08:09
10

@IvanGerasimenko it deletes rows from both tables. – Kevin Ortman Mar 14 '17 at 19:56
7

@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) – yohannc Mar 22 '17 at 13:50
FYI, both tables need to have a primary key in this case, else a weird exception will be thrown. – Mark Giaconia Sep 26 '17 at 15:37
5

if you do `select pf.*` it will only delete the productfilters? – chillworld Mar 20 '18 at 08:01
2

This solution provides greater clarity and elegance than what I was attempting. Using a subquery also avoids more complex unions or joins, making it more efficient for larger record sets. I needed a quick solution to clear a subset of mixed data. Thank you. – Michael M Nov 27 '18 at 00:18
5

@chillworld Yes. I tested it. Also, this should be the accepted answer. Far superior to the current. – Word Rearranger Mar 01 '19 at 18:16
Should indeed be the accepted answer. Gold! – tbdrz Jun 08 '23 at 07:26

davek · Accepted Answer · 2012-10-01T11:37:07.997

56

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 01 '12 at 11:37

answered Oct 01 '12 at 11:12

davek

22,499
9
75
95

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. – arjacsoh Oct 01 '12 at 11:30
rowid is ambiguous without its alias. For a delete with join, the query is fine. – Gregory Bologna Jan 22 '21 at 17:10

score 4 · Answer 3 · answered Jan 22 '21 at 17:17

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

score 2 · Answer 4 · answered Feb 01 '22 at 21:11

2

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 01 '22 at 21:11

Nicolaesse

2,554
12
46
71

score -1 · Answer 5 · edited Jun 21 '21 at 01:02

-1

Please use a subquery

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

edited Jun 21 '21 at 01:02

MertG

753
1
6
22

answered Jun 18 '21 at 17:05

maniram reddy

7
1
8

Delete with "Join" in Oracle sql Query

5 Answers5

Linked