delete unmatched records from first table

Question

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

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

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

Answer 1

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
)