I have 3 UPDATE statements (that update more than one column) that I would like to execute against a list of id's without having to run the 3 update statements one by one against each id.
Here are the 3 update statements I need to run against a bunch of ids:
-- store ContractDate value in temp col
update tval set temp_col = (select val from tval where id = 402280209 and fid = 3782) where id = 402280209 and fid = 3782
-- Replace ContractDate with maturityDate
update tval set val= (select val from tval where fid = 3771 and id = 402280209) where fid = 3782 and id = 402280209
-- set MaturityDate to ContactDate
update tval set val = (select temp_col from tval where id = 402280209 and fid = 3782) where id = 402280209 and fid = 3771
I have a list of id's that I need to run the above 3 update statement against. Is it possible to run the above as a batch (i.e. in one query)?
For reference my tval table looks something like this:
id fid ts val temp_col
402280209 3765 2021-09-20 00:00:00.000 2023-12-19 00:00:00.000
402280209 3771 2021-09-20 00:00:00.000 2023-09-20 00:00:00.000 <---- I would like to swap this value
402280209 3782 2021-09-20 00:00:00.000 2023-12-19 00:00:00.000 <----- with this value
What I am trying to avoid is running the above manually for each id.
So my desired result is:
id fid ts val temp_col
402280209 3765 2021-09-20 00:00:00.000 2023-12-19 00:00:00.000
402280209 3771 2021-09-20 00:00:00.000 2023-12-19 00:00:00.000
402280209 3782 2021-09-20 00:00:00.000 2023-09-20 00:00:00.000
It seems from later clarification that you want to swap values in two different rows not columns. That is more complex. One option is to use a (constructed?) table to join both source and target rows. As long as both directions are present in this join table then it will swap them correctly.
update tval
set val = source.val
from (values
(3771, 3782),
(3782, 3771)
) v(fidTarget, fidSource)
join tval source
on source.fid = v.fidSource
where tval.id = 402280209
and v.fidTarget = tval.fid
and source.id = tval.id;