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'

Answer 1

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

Answer 2

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, '[email protected]', 'Hollis', 'Connell'),
  (2, '[email protected]', 'Robert', 'Duncan')
) as u2(id, email, first_name, last_name)
where u2.id = u.id;

Answer 3

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

Answer 4

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

Answer 5

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;

Answer 6

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.

Answer 7

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;

Answer 8

The answer provided by @zero323 works great on Postgre 12. In case, someone has multiple values for column_b (referred in OP's question)

UPDATE conupdate SET orientation_status = CASE
   when id in (66934, 39) then 66
   when id in (66938, 49) then 77
END
WHERE id IN (66934, 39, 66938, 49)

In the above query, id is analogous to column_b; orientation_status is analogous to column_a of the question.

Answer 9

@Roman thank you for the solution, for anyone using node, I made this utility method to pump out a query string to update n columns with n records.

Sadly it only handles n records with the same columns so the recordRows param is pretty strict.

const payload = {
  rows: [
    {
        id: 1,
        ext_id: 3
    },
    {
        id: 2,
        ext_id: 3
    },
    {
        id: 3,
        ext_id: 3
    } ,
        {
        id: 4,
        ext_id: 3
    } 
  ]
};

var result = updateMultiple('t', payload);

console.log(result);
/*
qstring returned is:

UPDATE t AS t SET id = c.id, ext_id = c.ext_id FROM (VALUES (1,3),(2,3),(3,3),(4,3)) AS c(id,ext_id) WHERE c.id = t.id
*/



function updateMultiple(table, recordRows){
  var valueSets = new Array();
  var cSet = new Set();
  var columns = new Array();
  for (const [key, value] of Object.entries(recordRows.rows)) {
    var groupArray = new Array();
    for ( const [key2, value2] of Object.entries(recordRows.rows[key])){    
      if(!cSet.has(key2)){
        cSet.add(`${key2}`);
        columns.push(key2);
      }
      groupArray.push(`${value2}`); 
    }
    valueSets.push(`(${groupArray.toString()})`);
  }
  var valueSetsString = valueSets.join();  
  var setMappings = new String();
  for(var i = 0; i < columns.length; i++){
    var fieldSet = columns[i];
    
      setMappings += `${fieldSet} = c.${fieldSet}`;
      if(i < columns.length -1){
        setMappings += ', ';
      }
  }
  var qstring = `UPDATE ${table} AS t SET ${setMappings} FROM (VALUES ${valueSetsString}) AS c(${columns}) WHERE c.id = t.id`;
  return qstring;
}

Answer 10

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;