使用不同的条件更新表的不同列

Question

我这里有一个sql:

update table_name
set column1 = new_value1,
column2 = new_value2

where id = 1;
update table_name
set column3 = new_value3,
column4 = new_value4

where id = 3;
update table_name
set column5 = new_value5,
column6 = new_value6,
column7 = new_value7

where id = 6;

有什么办法可以让它更简单或更干净吗?

Answer 1

我们可以将其重写为在同一个update语句中运行，其中它为WHERE子句中匹配的每个id设置所有列，并在每个列上使用case表达式来决定设置相同的值或新值:

update table_name
set column1 = case when id = 1 then 'new_value1' else column1 end,
column2 = case when id = 1 then 'new_value2' else column2 end,
column3 = case when id = 3 then 'new_value3' else column3 end,
column4 = case when id = 3 then 'new_value4' else column4 end,
column5 = case when id = 6 then 'new_value5' else column5 end,
column6 = case when id = 6 then 'new_value6' else column6 end,
column7 = case when id = 6 then 'new_value7' else column7 end
where id IN (1, 3, 6);

我们可以进一步修改它，在表值构造函数上使用JOIN表达式:

UPDATE table_name t
INNER JOIN ( VALUES 
ROW (1, 'new_value1', 'new_value2', NULL, NULL, NULL, NULL, NULL),
ROW (3, NULL, NULL, 'new_value3', 'new_value4', NULL, NULL, NULL),
ROW (6, NULL, NULL, NULL, NULL, 'new_value5', 'new_value6', 'new_value7')
) map(id, nv1, nv2, nv3, nv4, nv5, nv6, nv7) ON map.id = t.id
SET t.column1 = coalesce(map.nv1, t.column1),
t.column2 = coalesce(map.nv2, t.column2),
t.column3 = coalesce(map.nv3, t.column3),
t.column4 = coalesce(map.nv4, t.column4),
t.column5 = coalesce(map.nv5, t.column5),
t.column6 = coalesce(map.nv6, t.column6),
t.column7 = coalesce(map.nv7, t.column7);

两种方法都具有自动确保所有内容都在同一事务内的优点(更好的原子性，如果这甚至是一个词的话)，我喜欢第二种选择-尽管它稍微多一点代码-因为它处理新值的方式更像数据。

…但

我不会称它们为更"简单"的了。或";cleaner"。在这方面，您已经拥有的可能已经足够好了，除非您真的想要单语句的好处。

Answer 2

使用参数创建函数或触发器。如果您有要设置的列列表和要检查的列列表，请将数组作为参数传递给函数，并使用循环来构建字符串，而不是使用下面的语句。在这个例子中，我只是为每个函数都设置了变量，而不是函数中的参数(作为变量)。

然后像这样使用动态SQL

DECLARE sc1 varchar:= 'column1';
DECLARE sv1 varchar := 'new_value1';
DECLARE sc2 varchar:= 'column2';
DECLARE sv2 varchar := 'new_value2';
DECLARE wc1 varchar := 'id';
DECLARE wv1 varchar := '1';
EXECUTE 'update table_name set ' || sc1 || ' = ' || sv1 || ', ' || sc2 || ' = ' || sv2 || ' where ' || wc1 || ' = ' || wv1

---

或

create function build_query(sc1 varchar, 
sv1 varchar,
sc2 varchar,
sv2 varchar,
wc1 varchar,
wv1 varchar)
returns text
as
$$
begin
return EXECUTE 'update table_name set ' || sc1 || ' = ' || sv1 || ', ' || sc2 || ' = ' || sv2 || ' where ' || wc1 || ' = ' || wv1 ;
end;
$$
language plpgsql;

调用函数:

SELECT build_query('column1', 'new_value1', 'column2', 'new_value2', 'id', '1');