Don't update column if update value is null

SqlPostgresqlNullSql Update

Sql Problem Overview


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;

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.

The way I am doing it now is:

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;

Is there a better way?

Sql Solutions


Solution 1 - Sql

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;

Solution 2 - Sql

Neat trick, thanks Przemek, Frank & Erwin!

I suggest a minor edit to Erwin's answer to avoid empty updates. If any parameters were null (meaning: "use the old value"), the row was updated each time even though the row values did not change (after the first update).

By adding "param_x IS NOT NULL", we avoid empty updates:

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
     ...
 );

Solution 3 - Sql

Additionally, to avoid empty updates:

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
      ...
     );

This assumes target columns to be defined NOT NULL. Else, see Geir's extended version.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionPrzemekView Question on Stackoverflow
Solution 1 - SqlFrank HeikensView Answer on Stackoverflow
Solution 2 - SqlGeir BostadView Answer on Stackoverflow
Solution 3 - SqlErwin BrandstetterView Answer on Stackoverflow