SQL 如果更新值为空，则不更新列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13305878/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Don't update column if update value is null
提问by Przemek
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_xis a parameter of my function. Is there a way to NOT update those columns, for which the param is NULL? For example - if param_4and param_5are NULL, then update only the first three columns and leave old values for column_4and column_5.
param_x我的函数的参数在哪里。有没有办法不更新参数为的那些列NULL？例如-如果param_4和param_5是NULL，然后只更新前三列，离开旧值column_4和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?
有没有更好的办法？
回答by Frank Heikens
Drop the SELECT statement, there is no need for, just use the current value:
去掉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;
回答by Geir Bostad
Neat trick, thanks Przemek, Frank & Erwin!
巧妙的技巧，感谢 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).
我建议对 Erwin 的答案进行小幅编辑，以避免空更新。如果任何参数为空（意思是：“使用旧值”），即使行值没有改变（在第一次更新之后），每次也会更新该行。
By adding "param_x IS NOT NULL", we avoid empty updates:
通过添加“param_x IS NOT NULL”，我们可以避免空更新：
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
...
);
回答by Erwin Brandstetter
Additionally, to avoid emptyupdates:
此外，为避免空更新：
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.
这假定要定义目标列NOT NULL。否则，请参阅Geir 的扩展版本。
