遇到过类似的场景，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。

我认为公认的答案并不完全正确。它是顺序相关的。下面是一个用答案的方法不能正确工作的例子。

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;

所以问题仍然存在，有没有一种独立于顺序的方法?

----在尝试了一些事情后，这似乎是独立的秩序

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;

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

除了其他答案、注释和文档之外，数据类型转换还可以放在使用上。这允许更容易的复制粘贴:

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;

是的，你可以:

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

并工作证明:http://sqlfiddle.com/#!2/97c7ea / 1

你也可以使用update…从语法和使用映射表。如果你想要更新多个列，它是更一般化的:

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;

你可以添加任意多的列:

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演示