Oracle SQL 更新查询仅更新值为空的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9633398/
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
Oracle SQL update query only update values if they are null
提问by Tom
I have the following queries where I am updating values only if they are null.
我有以下查询，仅当它们为空时才更新值。
Is it possible to put all of these into a single query?
是否可以将所有这些放入一个查询中？
UPDATE test
SET test1 = 'hello'
WHERE test1 IS NULL
and
和
UPDATE test
SET test2 = 'world'
WHERE test2 IS NULL
回答by Ollie
You could try:
你可以试试：
UPDATE test
SET test1 = NVL(test1, 'hello'),
test2 = NVL(test2, 'world')
WHERE test2 IS NULL
OR test1 IS NULL;
Though it may fire your update triggers even for the rows that are effectively unchanged.
尽管它可能会触发您的更新触发器，即使对于实际上未更改的行也是如此。
回答by David Faber
UPDATE test
SET test1 = COALESCE(test1, 'hello')
, test2 = COALESCE(test2, 'hello')
WHERE test1 IS NULL OR test2 IS NULL
COALESCE() works similarly to NVL() in this circumstance -- returning the first non-null value.
在这种情况下，COALESCE() 的工作方式与 NVL() 类似——返回第一个非空值。
回答by Mikael Eriksson
UPDATE test
SET Test1 = COALESCE(test1, 'hello'),
Test2 = COALESCE(test2, 'world')
WHERE test1 IS NULL OR
test2 IS NULL
