我有一个这样的查询(在函数中):
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
是我的函数的参数。有没有办法不更新那些参数为的列NULL
?例如 - 如果param_4
and param_5
are NULL
,然后仅更新前三列并保留旧值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 语句,不需要,只需使用当前值:
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;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)