Don't update column if update value is null

Question

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?

I think that's the way to go. – Erkan Haspulat Nov 09 '12 at 10:19 — Erkan Haspulat, Nov 09 '12 at 10:19

score 132 · Accepted Answer · answered Nov 09 '12 at 11:29

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;

Erwin Brandstetter · Answer 2 · 2019-06-04T13:21:28.477

22

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.

edited Jun 04 '19 at 13:21

answered Nov 09 '12 at 20:11

Erwin Brandstetter

605,456
145
1,078
1,228

Geir Bostad · Answer 3 · 2012-12-19T01:20:36.127

20

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

edited Dec 19 '12 at 01:20

answered Dec 19 '12 at 00:38

Geir Bostad

886
7
18

5

Right, that's even better. If all involved columns are defined `NOT NULL`, then my version already covers NULL in the parameters, though. Not unlikely for a case, where the OP disallows new NULL values in the UPDATE. – Erwin Brandstetter Apr 02 '14 at 00:24

score 0 · Answer 4 · answered Jul 11 '23 at 08:21

What about multiple queries? I guess it would be the best performance-wise if you don't rely on a unique ID, but want to update the whole database.

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

Don't update column if update value is null

4 Answers4

Linked