Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Delete with Left Join in Oracle 10g

I have the following code that works fine in MS SQL Server:

delete grp
from grp
left join my_data
on grp.id1 = my_data.id1
and grp.id2 = my_data.id2
and grp.id3 = my_data.id3
and grp.id4 = my_data.id4
where my_data.id1 is NULL

Basically, I want to delete all occurrence that can be found in grp and don't have any equivalence in my_data. Sadly, it doesn't work in Oracle 10g. I tried using the old syntax for left join (+) but it doesn't work either. Like this:

delete grp
from grp,
my_data
where grp.id1 = my_data.id1 (+)
and grp.id2 = my_data.id2 (+)
and grp.id3 = my_data.id3 (+)
and grp.id4 = my_data.id4 (+)
and my_data.id1 is NULL

A IN clause would works if I didn't have multiple keys but I don't see how I could use it with my data. So, what is the alternative?

like image 282
Simon T. Avatar asked Sep 08 '10 22:09

Simon T.


People also ask

Can we use LEFT join IN delete query?

We can also use the LEFT JOIN clause in the DELETE statement to delete rows in a table (left table) that does not have matching rows in another table (right table). Note that we only put T1 table after the DELETE keyword, not both T1 and T2 tables like we did with the INNER JOIN clause.

How do you delete using LEFT join?

Delete left join table is used to delete rows from the left table that do not have matching records in the right table. Below is the syntax to of deleting rows with a left join that does not have matching rows in another table: Delete table1 from table1 LEFT JOIN table2 ON table1. col_name=table2.

Can we use delete with join?

A DELETE statement can include JOIN operations. It can contain zero, one, or multiple JOIN operations. The DELETE removes records that satisfy the JOIN conditions.


2 Answers

Shannon's solution is the way to go: use the operator NOT IN (or NOT EXISTS).

You can however delete or update a join in Oracle, but the synthax is not the same as MS SQL Server:

SQL> DELETE FROM (SELECT grp.*
  2                  FROM grp
  3                  LEFT JOIN my_data ON grp.id1 = my_data.id1
  4                                   AND grp.id2 = my_data.id2
  5                                   AND grp.id3 = my_data.id3
  6                                   AND grp.id4 = my_data.id4
  7                 WHERE my_data.id1 IS NULL);

2 rows deleted

Additionally, Oracle will only let you update a join if there is no ambiguity as to which base row will be accessed by the statement. In particular, Oracle won't risk an update or a delete (the statement will fail) if there is a possibility that a row may appear twice in the join. In this case, the delete will only work if there is a UNIQUE constraint on my_data(id1, id2, id3, id4).

like image 105
Vincent Malgrat Avatar answered Oct 06 '22 12:10

Vincent Malgrat


Tables and data:

SQL> create table grp (id1 number null, id2 number null, id3 number null, id4 number null);    
Table created.

SQL> create table my_data (id1 number null, id2 number null, id3 number null, id4 number null);

Table created.

SQL> insert into grp values (1, 2, 3, 4);

1 row created.

SQL> insert into grp values (10, 20, 30, 40);

1 row created.

SQL> insert into grp values (1, 2, 30, 40);

1 row created.

SQL> insert into my_data values (1, 2, 3, 4);

1 row created.

SQL> commit;

Commit complete.

Using in. Note Do not use if the IDs in the subquery can be null. Not in of null never returns true.

SQL> delete grp where (id1, id2, id3, id4) not in (select id1, id2, id3, id4 from my_data);

2 rows deleted.

SQL> select * from grp;

       ID1        ID2        ID3        ID4
---------- ---------- ---------- ----------
         1          2          3          4

Using exists

SQL> rollback;

Rollback complete.

SQL> delete grp where not exists (select * from my_data where grp.id1 = my_data.id1 and grp.id2 = my_data.id2 and grp.id3 = my_data.id3 and grp.id4 = my_data.id4);

2 rows deleted.

SQL> select * from grp;

       ID1        ID2        ID3        ID4
---------- ---------- ---------- ----------
         1          2          3          4

SQL> 
like image 45
Shannon Severance Avatar answered Oct 06 '22 10:10

Shannon Severance