如果更新值为空，则不更新列 [英] Don't update column if update value is null

问题描述

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

I have a query like this (in a function):

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 是我函数的参数。有没有一种方法可以不更新那些参数为 NULL 的列？例如-如果 param_4 和 param_5 为 NULL ，则仅更新前三列，并保留 column_4 和 column_5 的旧值。

Where param_x is a parameter of my function. Is there a way to NOT update those columns, for which the param is NULL? For example - if param_4 and param_5 are NULL, then update only the first three columns and leave old values for column_4 and 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;

有更好的方法吗？

推荐答案

拖放SELECT语句，无需使用，只需使用当前值：

Drop the SELECT statement, there is no need for, just use the current value:

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;

这篇关于如果更新值为空，则不更新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！