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

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;

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

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;

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

假设你有一个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;

你也可以使用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演示

@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。