CODEWITHSUNDEEP
c#sqloracle-databasemerge
SwagiWagi
SwagiWagi

Reputation: 441

Doing a MERGE without replacing null values in Oracle

I need to make an upsert to a table.

Using Oracle.

  1. I created a temporary table that has the same columns as the original.
  2. I want to merge the temp one into the original but without replacing null values from the temp table, i.e:

table1:

Id, First_Name, Last_Name, Salary
123, Bob, Foo, 100

table2:

Id, First_Name, Last_Name, Salary
123, NULL, NULL, 200

After merge:

table1:

Id, First_Name, Last_Name, Salary
123, Bob, Foo, 200

I tried constructing this:

MERGE INTO t1 wu USING t2 tmp ON (wu.Id = tmp.Id) WHEN MATCHED THEN
CASE WHEN tmp.First_Name IS NOT NULL THEN
UPDATE SET wu.First_Name = tmp.First_Name 
ELSE
UPDATE SET wu.First_Name = wu.First_Name
END
CASE WHEN tmp.Last_Name IS NOT NULL THEN
UPDATE SET wu.Last_Name = tmp.Last_Name 
ELSE
UPDATE SET wu.Last_Name = wu.Last_Name 
END
CASE WHEN tmp.Salary IS NOT NULL THEN
UPDATE SET wu.Salary = tmp.Salary 
ELSE
UPDATE SET wu.Salary = wu.Salary 
END
WHEN NOT MATCHED THEN
INSERT VALUES(tmp.Id, tmp.First_Name, LastName, tmp.Salary);

But I get:

ORA-00927: missing equal sign

I do not want to use PL/SQL - all from c# directly.

Upvotes: 0

Views: 2381

Answers (1)

Littlefoot
Littlefoot

Reputation: 142968

NVL might help.

Test case:

SQL> create table t1 (id number, first_name varchar2(10),
  2    last_name varchar2(10), salary number);

Table created.

SQL> create table t2 (id number, first_name varchar2(10),
  2    last_name varchar2(10), salary number);

Table created.

SQL> insert all
  2    into t1 values (123, 'Bob', 'Foo', 100)
  3    into t2 values (123, null, null, 200)
  4    into t2 values (555, 'Little', 'Foot', 20)
  5  select * From dual;

3 rows created.

Merge:

SQL> merge into t1 using t2 on (t1.id = t2.id)
  2  when matched then update set
  3    t1.first_name = nvl(t2.first_name, t1.first_name),
  4    t1.last_name  = nvl(t2.last_name , t1.last_name),
  5    t1.salary     = nvl(t2.salary    , t1.salary)
  6  when not matched then insert
  7    values (t2.id, t2.first_name, t2.last_name, t2.salary);

2 rows merged.

Result:

SQL> select * from t1 order by id;

        ID FIRST_NAME LAST_NAME      SALARY
---------- ---------- ---------- ----------
       123 Bob        Foo               200
       555 Little     Foot               20

SQL>

Upvotes: 3

Related Questions