I can update fine and I can delete fine, but I want to do both at once.
I have a table ORIG like this, column names are
ref,fname,lname,add1,add2,add3,add4
A1 a b c d h j
S2 f d s e y t
B3 j f s e o p
Where the first column is unique
then another table DATA like this
ref,fname,lname,add1,add2
A1 b c d e
B3 k g h t
I want to update the first table using the second and delete any rows that don't have the unique
So the end result needs to be table ORIG like below
A1 b c d e h j
B3 k g h t o p
Am I able to accomplish this in one go?
I might approach this using CTEs:
with u as (
update orig
set b = d.b, . . .
from data d
where d.a = orig.a
returning *
)
delete from orig
where not exists (select 1 from u where u.a = d.a);
The first CTE updates the rows. The second one does the delete.
You can use a correlated subquery for this:
delete
from orig o
where not exists (
select 1
from data d
where d.col1 = o.col1
)