Delete data from table1 based on data in table 2 with composite primary key-CodePudding

I have two tables in Oracle db. Table1 and table2. Both have composite primary key. Structure of the tables are same but not data. I need to delete rows from table1 which are not in table2.

CodePudding user response：

You can do:

delete from t
where (a, b) not in (
  select a, b from u
)

See running example at db<>fiddle.

CodePudding user response：

There are various options; you've already seen one in Impaler's post.

SQL> create table a (id1, id2, name) as
  2    select 1, 10, 'Little' from dual union all
  3    select 2, 20, 'Foot'   from dual union all  --> these two should be deleted
  4    select 3, 30, 'Xyz'    from dual;           --> as (id1, id2) don't exist in table B

Table created.

SQL> create table b (id1, id2, name) as
  2    select 1, 10, 'Mickey' from dual union all
  3    select 4, 40, 'Mouse'  from dual;

Table created.

NOT IN (the same as Impaler's, obviously):

SQL> delete from a
  2    where (id1, id2) not in (select id1, id2 from b);

2 rows deleted.

SQL> select * from a;

       ID1        ID2 NAME
---------- ---------- ------
         1         10 Little


SQL> rollback;

Rollback complete.

NOT EXISTS:

SQL> delete from a
  2    where not exists (select null from b
  3                      where b.id1 = a.id1
  4                        and b.id2 = a.id2
  5                     );

2 rows deleted.

SQL> select * from a;

       ID1        ID2 NAME
---------- ---------- ------
         1         10 Little


SQL> rollback;

Rollback complete.

IN, with the MINUS set operator:

SQL> delete from a
  2  where (id1, id2) in (select id1, id2 from a
  3                       minus
  4                       select id1, id2 from b
  5                      );

2 rows deleted.

SQL> select * from a;

       ID1        ID2 NAME
---------- ---------- ------
         1         10 Little


SQL> rollback;

Rollback complete.

SQL>