CODEWITHSUNDEEP
sqloracle-databasesql-updatesql-merge
kamal
kamal

Reputation: 1

Update through merge

I have a design problem while creating a procedure.

Suppose I have to update all rows in a table using data in other columns in same row. Say table1 has 3 columns A, B and C and I need to update all rows as C=A+B. So I can use:

update table1 set C=A+B;

But I need to do this using something like below:

merge tab1e1 using (some query) on (some condition)
when matched update
 C=A+B
when not matched 
null;

Is there a way of doing this by manipulating 'some query' and 'some condition'?

Upvotes: 0

Views: 83

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

I don't really understand why you'd want to use a merge instead of an update, but if you really have to, you can use dual to create your using clause and an on condition that is always true:

merge into table1
using (select null from dual)
on (1 = 1)
when matched then update set c = a + b;

With some sample data:

create table table1 (a number, b number, c number);
insert into table1 values (1, 2, null);
insert into table1 values (3, 4, null);
insert into table1 values (5, 6, null);

merge into table1
using (select null from dual)
on (1 = 1)
when matched then update set c = a + b;

3 rows merged.

select * from table1;

         A          B          C
---------- ---------- ----------
         1          2          3 
         3          4          7 
         5          6         11

SQL Fiddle.

Upvotes: 1

Related Questions