使用PostgreSQL更新同一查询中的多行 [英] Update multiple rows in same query using PostgreSQL
本文介绍了使用PostgreSQL更新同一查询中的多行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在寻找在一条语句中更新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'
推荐答案
您还可以使用 update ... from
语法并使用映射表.如果您要更新多个列,则可以更通用:
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;
这篇关于使用PostgreSQL更新同一查询中的多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文