表格和数据:
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,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,40);
1 row created.
sql> insert into my_data values (1,4);
1 row created.
sql> commit;
Commit complete.
使用in.注意如果子查询中的ID可以为空,请勿使用.不在null不会返回true.
sql> delete grp where (id1,id2,id3,id4) not in (select id1,id4 from my_data);
2 rows deleted.
sql> select * from grp;
ID1 ID2 ID3 ID4
---------- ---------- ---------- ----------
1 2 3 4
使用存在
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>