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?

user_14584930 · Answer

Either Vincent's answer https://stackoverflow.com/a/3675205 does not work at all, or it does not work in Oracle 12c. That answer should be improved by specifying the lowest or highest version of Oracle where this works. The proof:

SELECT * FROM v$version where banner like 'Oracle%';
/*
Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
*/
create table a (id int);
create table b (id int);
insert into a select 1 from dual union select 2 from dual;
insert into b select 1 from dual union select 2 from dual union select 3 from dual;
select * from a right join b on b.id = a.id;
/*
1   1
2   2
null    3
*/
delete from (
  select b.*
  from b
  inner join a on a.id = b.id
)    
/*
Error at Command Line : 7 Column : 13
Error report -
SQL Error: ORA-01752: cannot delete from view without exactly one key-preserved table
01752. 00000 -  "cannot delete from view without exactly one key-preserved table"
*Cause:    The deleted table had
           - no key-preserved tables,
           - more than one key-preserved table, or
           - the key-preserved table was an unmerged view.
*Action:   Redefine the view or delete it from the underlying base tables.
*/

delete from b
where rowid in (
  select b.rowid
  from b
  inner join a on a.id = b.id
)
/*
2 rows deleted.
*/
select * from a right join b on b.id = a.id
/*
null  3
*/

drop table a;
drop table b;

Bottom line is, use WHERE ROWID IN () at least in 12c.

user_7790020 · Answer

I can't add a comment because it need 50 reps,so I add a answer here.

I tested Vincent's delete from query, that syntax can't let you delete what you want,at least it's not a common use for all the delete join cases.

At first I create a table using oracle default user scott:

create table emp1 as select * from emp where sal<2000;

I want to delete the records from emp where empno in emp1(just a simple test),so I used this delete from query:

delete from (select a.* from emp a join emp1 b on a.empno=b.empno);

No matter what the table or join order is,left join or inner join,no matter what where clause I use,the sql will delete the corresponding records in emp1.

So I think this delete from query can not let you delete from a specified table. Loop a cursor will be a better way for these cases.

Delete with Left Join in Oracle 10g

5 Answers