oracle10g - Delete with Left Join in Oracle 10g

Question

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?

Answer 1

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