UPDATE option of MERGE and ordinar MERGE

Q: Can I use MERGE with UPDATE option instead ordinar UPDATE.

Yes, you can but in a single-user environment. MERGE doesn't work like ordinar UPDATE in multi-user system.

Let's create two tables:

SQL> create table t_s (on_column number(2), name varchar2(10))
2 /

Table created.

SQL> create table t_d (id number, on_column number(2), name varchar2(10))
2 /

Table created.

SQL> insert into t_s values(10,'table 1')
2 /

1 row created.

SQL> insert into t_s values(20,'table 1')
2 /

1 row created.

SQL> begin
2 for i in 1..4 loop
3 insert into t_d values(i,20,null);
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> alter table t_s add constraint t_s_pk primary key (on_column)
2 /

Table altered.


Now we open the new Oracle session and change ON_COLUMN value from 20 to 50 of T_D table in one of the row:

SQL> update t_d set on_column = 50 where id = 3;

1 row updated.

Now in the first session we update NAME column of T_D the using corresponding
value of T_S (ordinar UPDATE):

SQL> update t_d
2 set name =
3 (select name from t_s where t_s.on_column = t_d.on_column)
4 where exists
5 (select null from t_s where t_s.on_column = t_d.on_column)
6 /

Naturally it hangs until COMMIT in the second session:

SQL> update t_d set on_column = 50 where id = 3;

1 row updated.

SQL> commit;

Commit complete.

Now we have the results of UPDATE:

SQL> update t_d
2 set name =
3 (select name from t_s where t_s.on_column = t_d.on_column)
4 where exists
5 (select null from t_s where t_s.on_column = t_d.on_column)
6 /

3 rows updated.

SQL> select * from t_d;

ID ON_COLUMN NAME
---------- ---------- ----------
1 20 table 1
2 20 table 1
3 50
4 20 table 1

This is exactly what Oracle does using the principle of "serialized snapshot" for
DML statements. The same results we have for updatable in-line wiew:

SQL> update
2 (select t_s.name sname, t_d.name dname
3 from t_s, t_d where t_s.on_column = t_d.on_column)
4 set dname = sname
5 /

3 rows updated.

SQL> select * from t_d;

ID ON_COLUMN NAME
---------- ---------- ----------
1 20 table 1
2 20 table 1
3 50
4 20 table 1


MERGE works differ. Compare the results after the same actions:

SQL> merge into t_d
2 using t_s
3 on (t_s.on_column = t_d.on_column)
4 when matched then
5 update set t_d.name = t_s.name
6 /

4 rows merged.

SQL> select * from t_d;

ID ON_COLUMN NAME
---------- ---------- ----------
1 20 table 1
2 20 table 1
3 50 table 1
4 20 table 1


Constituing on Tom Kyte's explanation what MERGE statement emulates the code

for v in (select .... ) loop
update ....;
if sql%rowcount = 0 then
insert ....;
end if;
end loop;

we can think what MERGE operations work like WHERE CURRENT OF construction.
Oracle doesn't specify the details of the MERGE realization but
in favor of this theory two facts say. The first one is what
UPDATE clause of MERGE can't update joined columns of a destination table
- that is not prohibited in an ordinar UPDATE. The second fact is
we can force MERGE to work like an ordinar UPDATE using row-level triggers:

SQL> create or replace trigger force_like_update
2 before update on t_d
3 for each row
4 declare
5 on_column t_d.on_column%type;
6 begin
7 on_column := :old.on_column;
8 end;
9 /

Trigger created.

SQL> merge into t_d
2 using t_s
3 on (t_s.on_column = t_d.on_column)
4 when matched then
5 update set t_d.name = t_s.name
6 /

3 rows merged.

SQL> select * from t_d;

ID ON_COLUMN NAME
---------- ---------- ----------
1 20 table 1
2 20 table 1
3 50
4 20 table 1

This trigger causes the restart of MERGE because it uses ON_COLUMN value
and there are differ versions of the consistent read block and the current read
block (like it happens in an ordinar UPDATE).