Update multiple rows in same query using PostgreSQL

Question

I m looking to update multiple rows in PostgreSQL in one statement  Is there a way to do something like the following   UPDATE table  SET   column a   1 where column b    123    column a   2 where column b    345

User · Answer

Came across similar scenario and the CASE expression was useful to me.

UPDATE reports SET is_default = 
case 
 when report_id = 123 then true
 when report_id != 123 then false
end
WHERE account_id = 321;

Reports - is a table here, account_id is same for the report_ids mentioned above. The above query will set 1 record (the one which matches the condition) to true and all the non-matching ones to false.

User · Answer

You can also use update     from syntax and use a mapping table  If you want to update more than one column  it s much more generalizable   update test as t set     column a   c column a from  values       123   1         345   2      as c column b  column a   where c column b   t column b    You can add as many columns as you like   update test as t set     column a   c column a      column c   c column c from  values       123   1   ---          345   2             as c column b  column a  column c   where c column b   t column b    sql fiddle demo

User · Answer

I don t think the accepted answer is entirely correct  It is order dependent  Here is an example that will not work correctly with an approach from the answer  create table xxx       id varchar 64       is enabled boolean     insert into xxx  id  is enabled  values   1  true   insert into xxx  id  is enabled  values   2  true   insert into xxx  id  is enabled  values   3  true    UPDATE public xxx AS pns         SET is enabled           u is enabled             FROM               VALUES                         3               false                         1               true                         2               false                      AS u id  is enabled              WHERE u id   pns id   select   from xxx   So the question still stands  is there a way to do it in an order independent way  ---- after trying a few things this seems to be order independent UPDATE public xxx AS pns         SET is enabled           u is enabled             FROM               SELECT  3  as id  false as is enabled UNION             SELECT  1  as id  true as is enabled UNION             SELECT  2  as id  false as is enabled            as u             WHERE u id   pns id

User · Answer

For updating multiple rows in a single query  you can try this  UPDATE table name SET  column 1   CASE WHEN any column   value and any column   value THEN column 1 value end  column 2   CASE WHEN any column   value and any column   value THEN column 2 value end  column 3   CASE WHEN any column   value and any column   value THEN column 3 value end        column n   CASE WHEN any column   value and any column   value THEN column n value end   if you don t need additional condition then remove and part of this query

User · Answer

In addition to other answers  comments and documentation  the datatype cast can be placed on usage  This allows an easier copypasting  update test as t set     column a   c column a  number from  values       123   1         345   2      as c column b  column a   where t column b   c column b  text

User · Answer

Based on the solution of  Roman  you can set multiple values   update users as u set -- postgres FTW   email   u2 email    first name   u2 first name    last name   u2 last name from  values    1   hollis weimann biz    Hollis    O  Connell       2   robert duncan info    Robert    Duncan     as u2 id  email  first name  last name  where u2 id   u id

User · Answer

Let s say you have an array of IDs and equivalent array of statuses - here is an example how to do this with a static SQL  a sql query that doesn t change due to different values  of the arrays    drop table if exists results dummy  create table results dummy  id int  status text  created at timestamp default now    updated at timestamp default now     -- populate table with dummy rows insert into results dummy  id  status  select unnest array 1 2 3 4 5   int    as id  unnest array  a   b   c   d   e    text    as status   select   from results dummy   -- THE update of multiple rows with by different values update results dummy as rd set    status new status  updated at now   from  select unnest array 1 2 5   int    as id unnest array  a    b    e     text    as status  as new where rd id new id   select   from results dummy   -- in code using   IDs   as first bind variable and   statuses   as the second bind variable  update results dummy as rd set    status new status  updated at now   from  select unnest  1  int    as id unnest  2  text    as status  as new where rd id new id

User · Answer

Yes  you can   UPDATE foobar SET column a   CASE    WHEN column b    123  THEN 1    WHEN column b    345  THEN 2 END WHERE column b IN   123   345     And working proof  http   sqlfiddle com   2 97c7ea 1

[sql] Update multiple rows in same query using PostgreSQL

Examples related to sql

Examples related to postgresql