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.
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