Don't update column if update value is null
Solution 1
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
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
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.
Przemek
php, javascript / jQuery developer ios / objective-c in near future ;)
Updated on July 26, 2022Comments
-
Przemek almost 2 years
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 isNULL
? For example - ifparam_4
andparam_5
areNULL
, then update only the first three columns and leave old values forcolumn_4
andcolumn_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?
-
Erwin Brandstetter about 10 yearsRight, 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.