sqlsql-serversql-deletesql-except

Combine these two SQL statements into one


I have two tables t1, t2 with columns name, invoice, total.

I am using this query to get the rows that in T2 and not in T1

select * 
from t2  
where name = (select source from info) 
except 
(select * from t1)

It works fine and returning a couple rows, what I want is to delete these returned rows in one statement.

I've tried this, but it deletes all the rows in T2 not only the returned rows from the query.

delete from t2 
where exists ((select * 
               from t2  
               where name = (select source from info) 
               except 
               (select * from t1) 
             )) 

Here is a sample of my data:

T1

enter image description here

T2

enter image description here

THE RETURNED DATA (EXISTS IN T2 AND NOT IN T1 WHERE THE NAME IS C2)

enter image description here

The 3rd table info is get the name which is in this case is C2.

Thanks in advance.


Solution

  • You can do it with a left join of t2 to t1:

    delete t
    from (
      select * from t2
      where name = (select source from info)
    ) t left join t1
    on t1.name = t.name and t1.invoice = t.invoice and t1.total = t.total
    where t1.name is null
    

    See the demo.

    If you want to use NOT EXISTS:

    delete t
    from (
      select * from t2
      where name = (select source from info)
    ) t 
    where not exists (
      select 1 from t1
      where name = t.name and invoice = t.invoice and total = t.total
    )
    

    See the demo.

    Results (rows remaining in t2):

    > name | invoice | total
    > :--- | ------: | ----:
    > C1   |       1 |   150
    > C1   |       2 |   300
    > C2   |       1 |   200
    > C2   |       2 |   165