How can I execute multiple update statements for a list of id's

ghz 8months ago ⋅ 165 views

How can I execute multiple update statements for a list of id's in PostgresSQL all at once?

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

Answers

You can achieve this by combining the three update statements into a single query using conditional logic. Here's how you can do it:

WITH UpdatedValues AS (
    SELECT
        id,
        fid,
        CASE
            WHEN fid = 3782 THEN (SELECT val FROM tval WHERE fid = 3771 AND tval.id = ids.id)
            WHEN fid = 3771 THEN (SELECT temp_col FROM tval WHERE fid = 3782 AND tval.id = ids.id)
            ELSE val
        END AS new_val
    FROM
        tval
    WHERE
        id IN (/* Your list of ids */)
),
UpdatedTval AS (
    UPDATE tval
    SET val = uv.new_val
    FROM UpdatedValues uv
    WHERE tval.id = uv.id AND tval.fid = uv.fid
)
UPDATE tval
SET temp_col = uv.new_val
FROM UpdatedValues uv
WHERE tval.id = uv.id AND tval.fid = 3782;

In this query:

  • The UpdatedValues common table expression (CTE) calculates the new values based on the conditions specified in your update statements.
  • The UpdatedTval CTE updates the val column in the tval table based on the calculated new values.
  • Finally, the last UPDATE statement updates the temp_col column in the tval table based on the new values where fid is 3782.

Replace /* Your list of ids */ with the list of IDs against which you want to run these updates. This query will update all the specified IDs in one go, avoiding the need to run the updates manually for each ID.

#sql