ChatGPT解决这个技术问题 Extra ChatGPT

Update multiple rows in same query using PostgreSQL

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'
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.

R
Roman Pekar

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


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
Works great, thank you for clarifying! The Postgres documentation for this makes for a bit of a confusing read.
that's cool! did not expect to learn a new sql construct today
How dynamic build VALUES(...) ? i.e. add 100 rows to VALUES(). Can it be an array or something?
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; ; ``` stackoverflow.com/questions/57517980/…
A
Alex Hurst

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', 'Connell'),
  (2, 'robert@duncan.info', 'Robert', 'Duncan')
) as u2(id, email, first_name, last_name)
where u2.id = u.id;

This seems like his solution.. UPDATE FROM (VALUES...) WHERE. How is it only based?
I prefer this answer because the variable names make it easier to understand what is going on.
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.
z
zero323

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


This is wrong... You will update all rows, even if it is not '123' nor '345'. You should use WHERE column_b IN ('123','456')...
i think '456' supposed to be '345'
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.
That's not what he asked for.. he needs to update multiple cols, not set col A based on col B.
Isn't it exactly what OP asked for - only column_a needs updating (based on value of column_b), not multiple columns, right?
O
Omar

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


T
Tal Barda

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;

R
Ricky Boy

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.


r
rjdb123

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


A
Anton Bessonov

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;

B
Binita Bharati

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.


P
Peter Litvak

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;

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