Delete with Left Join in Oracle 10g

oracle, oracle10g

Solution

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>

Problem

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?

Original source