Combine these two SQL statements into one

Question

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

T2

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

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

Thanks in advance.

Answer 1

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

Answer 2

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

The query that I would use is:

select t2.*
from t2
where not exists (select 1 from t1 where t1.? = t2.name);

It is not clear what the name of the matching column is in t1.

This is readily turned into a delete:

delete from t2
where not exists (select 1 from t1 where t1.? = t2.name);

Answer 3

You want to delete from t2 with two conditions:

• name = (select source from info)
• row has no match in t1

The statement:

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

Or shorter with an IN clause, only available in DBMS that allow IN with tuples:

delete from t2
where name = (select source from info)
and (name, invoice, total) not in (select name, invoice, total from t1);