CODEWITHSUNDEEP
sqloracle-database
George Mauer
George Mauer

Reputation: 122172

Oracle: Is there a simple way to say "if null keep the current value" in merge/update statements?

I have a rather weak understanding of any of oracle's more advanced functionality but this should I think be possible.

Say I have a table with the following schema:

MyTable
  Id INTEGER,
  Col1 VARCHAR2(100),
  Col2 VARCHAR2(100)

I would like to write an sproc with the following 

PROCEDURE InsertOrUpdateMyTable(p_id in integer, p_col1 in varcahr2, p_col2 in varchar2)

Which, in the case of an update will, if the value in p_col1, p_col2 is null will not overwrite Col1, Col2 respectively

So If I have a record:

id=123, Col1='ABC', Col2='DEF'

exec InsertOrUpdateMyTable(123, 'XYZ', '098'); --results in id=123, Col1='XYZ', Col2='098'
exec InsertOrUpdateMyTable(123, NULL, '098');  --results in id=123, Col1='ABC', Col2='098'
exec InsertOrUpdateMyTable(123, NULL, NULL);   --results in id=123, Col1='ABC', Col2='DEF'

Is there any simple way of doing this without having multiple SQL statements?

I am thinking there might be a way to do this with the Merge statement though I am only mildly familiar with it.

EDIT: Cade Roux bellow suggests using COALESCE which works great! Here are some examples of using the coalesce kewyord. And here is the solution for my problem:

MERGE INTO MyTable mt
    USING (SELECT 1 FROM   DUAL) a
    ON (mt.ID = p_id)
    WHEN MATCHED THEN
        UPDATE
           SET mt.Col1 = coalesce(p_col1, mt.Col1), mt.Col2 = coalesce(p_col2, mt.Col2)
    WHEN NOT MATCHED THEN
        INSERT (ID, Col1, Col2)
        VALUES (p_id, p_col1, p_col2);

Upvotes: 0

Views: 4306

Answers (2)

Cade Roux
Cade Roux

Reputation: 89711

Using MERGE and COALESCE? Try this link for an example

with

SET a.Col1 = COALESCE(incoming.Col1, a.Col1)
    ,a.Col2 = COALESCE(incoming.Col2, a.Col2)

Upvotes: 2

Joe Skora
Joe Skora

Reputation: 14920

Change the call or the update statement to use

nvl(newValue, oldValue)

for the new field value.

Upvotes: 2

Related Questions