使用PostgreSQL更新同一查询中的多行

Question

我希望在一条语句中更新PostgreSQL中的多行。有没有一种方法可以做到以下几点？

UPDATE table 
SET 
 column_a = 1 where column_b = '123',
 column_a = 2 where column_b = '345'

Answer 1

是的，你可以：

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

Answer 2

基于@Roman的解决方案，可以设置多个值：

update users as u set -- postgres FTW
  email = u2.email,
  first_name = u2.first_name,
  last_name = u2.last_name
from (values
  (1, '[e-mail]', 'Hollis', 'Connell'),
  (2, '[e-mail]', 'Robert', 'Duncan')
) as u2(id, email, first_name, last_name)
where u2.id = u.id;

Answer 3

您还可以使用更新。。。从语法，使用映射表。如果要更新多个列，则更具通用性：

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小提琴演示版