Update through merge

Question

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'?

Why would that be a problem? You can just do `update set c=a+b` in the `when matched` clause. Making it a merge will restrict which rows you update - only you you know what `some query` and `some condition` need to be, and why you need to use merge at all? I don't understand what your actual question is, or what you're stuck on. A concrete example might help, this is rather vague. — Alex Poole, Jul 01 '14 at 18:05
Or do you mean you still want to update every row in the table; but that you want to do that via a merge anyway? Such that `when matched` is always true? If so... why? — Alex Poole, Jul 01 '14 at 18:12
Thanks for quick response.. actually i need to do by merge anyway. this is a super simplified version of a query that is created in run time. this is scenario wher the merge statement will be updating all row of a table using columns of itself — kamal, Jul 01 '14 at 18:29
apologies .. it was foolish question.. I don't lnow why i was struggling whole day with this :) I got the solution by using 'select 1 from dual' for (some query) and '1=1' for (some condition) — kamal, Jul 01 '14 at 18:34

score 1 · Answer 1 · answered Jul 01 '14 at 18:36

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.

Thanks Alex for your time .. it was a design constraint. Thats why i needed to do this update in 'merge way' — kamal, Jul 01 '14 at 18:45

Update through merge

1 Answers1