PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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 once 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   
402280209   3782    2021-09-20 00:00:00.000   2023-12-19 00:00:00.000

What I am trying to avoid is running the above manually for each id.

Tags:

3

Answers


  1. 0

    You can do

    update tval
set temp_col = val,
    val= (select val from tval where fid = 3771 and id = 402280209)
3782)
where fid = 3782 and id = 402280209

    that is, use the set keyword and then comma-separate what you actually want to set.

    Your algorithm seems to indicate that you want to store a value in a temporary column and then override your value and then copy back your temporary column. I would do the latest update in a separate query.

    EDIT

    Trying to update field of the first record and vice versa seems to work according to this example:

    create table tbl(id int, val int);

insert into tbl(id, val)
values(1, 1),
      (2, 2);

update tbl
set val = t2.val
from tbl t2
where tbl.id in (1, 2) and t2.id in (1, 2) and tbl.id <> t2.id;

    https://www.db-fiddle.com/f/nsY2HBX1Wy8f61dAL7ZtM7/1

    Login or Signup to reply.
  2. 0

    In Postgres, as with most other DBMSs apart from MySQL and MariaDB, you don’t need an intermediate column. The left- and right-hand sides are evaluated independently, rather than in top-down order.

    update tval
set ContractDate = maturityDate,
    maturityDate = ContractDate
where id = 402280209
  and fid in (3765, 3771, 3782);

    If you have multiple id, fid pairs as well, then you can use a joined update, either on a real table or a constructed VALUES table. In Postgres the syntax is like this (note that tval is not present in the FROM).

    update tval
set ContractDate = maturityDate,
    maturityDate = ContractDate
from (values
    (402280208, 3765),
    (402280209, 3771),
    (402280210, 3782)
) v(id, fid)
where v.id = tval.id and v.fid = tval.fid;

    db<>fiddle

    Login or Signup to reply.
  3. 0

    It looks to me that your true goal is to swap val values for the fids 3771 and 3782. If that is so, you don’t need temp_col at all.

    If you are on PostgreSQL ver 15 or later, you can use the MERGE sql statement. Otherwise, you can do the same thing with UPDATE…FROM. Major idea #1 is to design a query that contains the table keys and values you desire. Major idea #2 is to add a defensive mechanism in case one of the fid records does not exist. Your current triple update would mess up badly if one row were missing.

    The Merge statement would be:

    Merge into tval u Using (
    Select id, Case When fid=3771 Then 3782 Else 3771 End as fid
      , val, count(*) Over () as nbr
    From tval
    Where id=402280209 and fid in (3771,3782)
) v On u.id=v.id and u.fid=v.fid and v.nbr=2
When Matched Update Set val=v.val

    The older Update syntax would be

    Update tval u From ( <same query as above> ) v
    Where <same criteria as above>
Set val=v.val
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search