如果更新值为 null，则不更新列

Question

我有这样的查询（在函数中）：

UPDATE some_table SET
  column_1 = param_1,
  column_2 = param_2,
  column_3 = param_3,
  column_4 = param_4,
  column_5 = param_5
WHERE id = some_id;

其中 param_x是我功能的参数。有没有办法不更新这些列，而con是 NULL？例如 - 如果param_4和param_5是NULL，则仅更新前三列，然后为column_4和column_5留下旧值。

我现在这样做的方式是：

SELECT * INTO temp_row FROM some_table WHERE id = some_id;
UPDATE some_table SET
  column_1 = COALESCE(param_1, temp_row.column_1),
  column_2 = COALESCE(param_2, temp_row.column_2),
  column_3 = COALESCE(param_3, temp_row.column_3),
  column_4 = COALESCE(param_4, temp_row.column_4),
  column_5 = COALESCE(param_5, temp_row.column_5)
WHERE id = some_id;

有更好的方法吗？

Answer 1

删除选择语句，不需要，只需使用当前值：

UPDATE some_table SET
  column_1 = COALESCE(param_1, column_1),
  column_2 = COALESCE(param_2, column_2),
  column_3 = COALESCE(param_3, column_3),
  column_4 = COALESCE(param_4, column_4),
  column_5 = COALESCE(param_5, column_5)
WHERE id = some_id;

Answer 2

此外，为了避免 empty 更新：

UPDATE some_table SET
  column_1 = COALESCE(param_1, column_1),
  column_2 = COALESCE(param_2, column_2)
  ...
WHERE id = some_id;
AND  (param_1 IS DISTINCT FROM column_1 OR
      param_2 IS DISTINCT FROM column_2 OR
      ...
     );

这假定要定义的目标列NOT NULL。否则，请参阅Geir的扩展版本。

Answer 3

整洁的技巧，谢谢Przemek，Frank＆amp;Erwin！

我建议对Erwin的答案进行次要编辑，以避免空空更新。如果任何参数为null（含义："使用旧值"），则每次行都会更新行，即使行值没有更改（在第一个更新之后）。

通过添加" param_x不是null"，我们避免了空更新：

UPDATE some_table SET
    column_1 = COALESCE(param_1, column_1),
    column_2 = COALESCE(param_2, column_2),
    ...
WHERE id = some_id
AND  (param_1 IS NOT NULL AND param_1 IS DISTINCT FROM column_1 OR
      param_2 IS NOT NULL AND param_2 IS DISTINCT FROM column_2 OR
     ...
 );

Answer 4

多个查询呢？我想，如果您不依赖唯一的ID，那将是最好的性能，而是要更新整个数据库。

UPDATE some_table SET column_1 = param_1 WHERE param1 IS NOT NULL;
UPDATE some_table SET column_2 = param_2 WHERE param2 IS NOT NULL;
...