CODEWITHSUNDEEP
sqloracle-databasesql-delete
Ashish Shetkar
Ashish Shetkar

Reputation: 1467

delete unmatched records from first table

I have table 1 and table 2 - the left join representation, we can think of as below-

enter image description here

I want to perform delete on table 1 as below - the orange hi-lighted part shoud get deleted

enter image description here

I was trying something like this - but it does not work.

  delete from  MY_SCHEMA.TABLE1 DL   LEFT JOIN MY_SCHEMA.TABLE2  ERR_TB on 
   DL.DL_ID1 = ERR_TB.ERR_ID1    and  DL.DL_ID2 = ERR_TB.ERR_ID2   ;

is it possible to perform delete as shown in figure-2. I want to delete orange records from table 1.

Any help is appreciated, thank you in Advance :) Note - there are no PK and FK here , table1 and table2 have - 2 same columns - which are not inter related or dependent

Upvotes: 1

Views: 2774

Answers (3)

Sara Popa
Sara Popa

Reputation: 163

This works for me, I hope it will help you:

delete t1 from table1 t1
join table2 t2
on t1.id1 = t2.id1
where t1.id1 = t2.id1 and t1.id2 = t2.id2

Upvotes: 0

forpas
forpas

Reputation: 164214

Use NOT EXISTS to filter out the matching rows.
The remaining rows are the ones with no counterpart in table2 and these will be deleted.

DELETE FROM MY_SCHEMA.table1 DL 
WHERE NOT EXISTS (
    SELECT 1 FROM table2 ERR_TB 
     WHERE ERR_TB.id1 = DL.id1 
       AND ERR_TB.id2 = DL.id2
)

The above code is based on your join statement.
If you want only ERR_TB.id1 = DL.id1 or ERR_TB.id2 = DL.id2 then use 1 of the following:

DELETE FROM MY_SCHEMA.table1 DL 
WHERE NOT EXISTS (
    SELECT 1 FROM table2 ERR_TB 
     WHERE ERR_TB.id1 = DL.id1 
)

or

DELETE FROM MY_SCHEMA.table1 DL 
WHERE NOT EXISTS (
    SELECT 1 FROM table2 ERR_TB 
     WHERE  ERR_TB.id2 = DL.id2
)

Upvotes: 3

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

you may try not exists :

delete my_schema.table1 t1 
 where not exists ( select 1 from table2 t2 where t2.id = t1.id2 );

or minus :

delete my_schema.table1
 where id2 in ( select t1.id2 from table1 t1 minus
                select t2.id  from table2 t2  );

dbfiddle uk demo

Upvotes: 1

Related Questions