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'

Mousseline

I keep trying to find it on that page but I can't get it. I see where you can update multiple rows using one where statement, but I don't get how to update multiple rows each with it's own where statement. I also searched google and didn't find a real clear answer so I was hoping someone could provide a clear example on this. – Mousseline

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

Titos

Also, one may have to specify a correct data type. An example with a date: ... from (values ('2014-07-21'::timestamp, 1), ('2014-07-20', 2), ... Further details at the PostgreSQL Documentation – Sienna

Works great, thank you for clarifying! The Postgres documentation for this makes for a bit of a confusing read. – Levity

that's cool! did not expect to learn a new sql construct today – Skardol

How dynamic build VALUES(...) ? i.e. add 100 rows to VALUES(). Can it be an array or something? – Hemline

@Charlie木匠 if you want to build it dynamically then you probably have data somewhere? why can't you just update it from the table? – Titos

@RomanPekar thanks for the reply. Context: process 100 million rows, bulk fetch 100 rows each time, update 50 on table1, update 80 on table2, insert 70 on table3. for example. - The business logic is complicated, and cannot be done in Single SQL. ^_^ – Hemline

@Charlie木匠 I'd say - make another question with example and some test data, it's a bit too much for a omment – Titos

Fyi, this is the most closest version I'm looking for. We can dynamically add rows to array.: ``` SELECT s.id, s.marked_for_deletion, s.name FROM unnest( array[ ('2f888809-2777-524b-abb7-13df413440f5',true,'Salad fork'), ('f2924dda-8e63-264b-be55-2f366d9c3caa',false,'Melon baller'), ('d9ecd18d-34fd-5548-90ea-0183a72de849',true,'Fondue fork') ]::item[] ) s; ; ``` #57518480 – Hemline

What is the main reason of use c? – Magdalenamagdalene

@ambigus9: Presumably to give the columns names – Subset

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;

Hertha

This seems like his solution.. UPDATE FROM (VALUES...) WHERE. How is it only based? – Raggedy

I prefer this answer because the variable names make it easier to understand what is going on. – Sylvie

Wow. Precise and clear. I am trying to implement something like this in GoLang. So can I pass an array of structs in place for values? Something like this, from (values $1) Where $1 is an array of structs. In the above case, the strict would have id, first_name and last_name as properties. – Calorifacient

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

Unconditioned

If you add ELSE column_b after the last WHEN ? THEN ? line then the column will be set to it's current value, thus preventing what MatheusQI said would happen. – Martelli

That's not what he asked for.. he needs to update multiple cols, not set col A based on col B. – Aikoail

Isn't it exactly what OP asked for - only column_a needs updating (based on value of column_b), not multiple columns, right? – Mule

Works great with Postgre 12. Thanks! – Subroutine

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

Lavernelaverock

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;

Gertrudgertruda

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.

Debora

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;

Governess

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.

Subroutine

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;
}

Kroeger

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;

Matador

I guess there is a mistake with parentheses in the VALUES part of your query. It should be ` VALUES ('2', false), ('1', true), ('3', false)` and then it works fine – N