要在单个查询中更新多行，您可以尝试这样做

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

如果你不需要额外的条件，那么删除这个查询的一部分

假设你有一个id数组和等价的状态数组-这里有一个例子，如何用一个静态SQL(一个SQL查询不会因为数组的不同值而改变)来做到这一点:

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;

@Roman感谢你的解决方案，对于任何使用节点的人来说，我使用这个实用工具方法来抽出一个查询字符串，用n条记录更新n列。

不幸的是，它只处理n个具有相同列的记录，所以recordRows参数是非常严格的。

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

根据@Roman的解决方案，可以设置多个值:

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;

遇到过类似的场景，CASE表达式对我很有用。

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

Reports -是这里的一个表，account_id与上面提到的report_ids相同。上面的查询将1条记录(符合条件的那条)设置为true，所有不匹配的记录设置为false。

@zero323提供的答案在Postgre 12上很有效。如果有人对column_b有多个值(在OP的问题中引用)

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)

在上面的查询中，id类似于column_b;Orientation_status类似于问题的column_a。