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
Add a comment
|
3 Answers
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.
8 Comments
Ollie
The trigger issue will be relevent whether you use COALESCE or NVL. IMHO COALESCE is overkill for a straightforward null test.
David Faber
The only reason I would choose COALESCE over NVL is that it is standard while the latter is not. Either way is fine with me. :)
Ollie
Could you define "Standard"? I believe NVL has been used by Oracle for quite some time and this is an Oracle question.
David Faber
Sorry, ANSI standard. Also NVL evaluates both arguments while COALESCE returns the first non-null, only evaluating up to that point.
Ollie
I like the point about NVL evaluating both arguments and COALESCE only to the first non-null. For this question it would be barel noticeable but in the case of a subquery in the NVL or similar then it could be much more efficient.
|
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.
7 Comments
Tom
So will this only update test1 if test1 is null and test2 = "abc"??
Ollie
@Tom, it will only update test1 if it is null regardless of what is in (or not in) test2.
Tom
Do I still need the where in this case?... I will of course add a
WHERE user_id = 'myuserid'David Faber
Technically speaking it will update test1 if it's non-null but it will set it to its current value. That's why triggers can be an issue. They will fire even though you're updating a column to its current value.
Ollie
if you don't include the
WHERE clause to test for nulls, rows where both test1 and test2 are not null will needlessly be updated. |
UPDATE test
SET Test1 = COALESCE(test1, 'hello'),
Test2 = COALESCE(test2, 'world')
WHERE test1 IS NULL OR
test2 IS NULL
