There is another hint as well to bypass the check

BYPASS_UJVC

if this is a one time update and if you are sure that the result will deterministic

Will the perfomance be the same if we use the merge method to update Vs updating the join, in this case ?

"you update value, but you joined on value, so the update would do nothing"

Iam not sure why you say that. May be i was not explaining properly.

CREATE TABLE T(VALUE INTEGER);

CREATE TABLE S(FLD CHAR(1), OLD_VALUE INTEGER, NEW_VALUE INTEGER);

INSERT INTO T VALUES(2);

INSERT INTO T VALUES(3);

INSERT INTO S VALUES('A',1,5);

INSERT INTO S VALUES('A',2,6);

INSERT INTO S VALUES('A',3,7);

COMMIT;

UPDATE T 
   SET VALUE = (SELECT NEW_VALUE FROM S WHERE VALUE = OLD_VALUE AND FLD = 'A');

SELECT * FROM T;

VALUE
----------
5
6
7

ROLLBACK;

ALTER TABLE S ADD CONSTRAINT UNI UNIQUE(OLD_VALUE);

UPDATE (SELECT VALUE,NEW_VALUE FROM S,T WHERE VALUE = OLD_VALUE AND FLD = 'A') 
   SET VALUE = NEW_VALUE;

SELECT * FROM T;

VALUE
----------
5
6
7

ROLLBACK;

MERGE INTO T
USING (SELECT * FROM S WHERE FLD = 'A') S
ON (T.VALUE = S.OLD_VALUE)
WHEN MATCHED THEN
UPDATE SET VALUE = OLD_VALUE;

UPDATE allows me to join on VALUE but MERGE dont i get the error.

ERROR at line 3:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "T"."VALUE"

Why is it so..

And again problem with JOIN UPDATE comes when i want to insert

INSERT INTO S VALUES('B',1,5);

I cant do this as i have a unique constraint on OLD_VALUE. And if i remove it then JOIN UPDATE does not work.

So all i have is is only this form of update.

UPDATE T 
   SET VALUE = (SELECT NEW_VALUE FROM S WHERE VALUE = OLD_VALUE AND FLD = 'A')
WHERE EXISTS(SELECT NEW_VALUE FROM S WHERE VALUE = OLD_VALUE AND FLD = 'A';

I think i have to live with the additional expence of checking for the existance of the data in the source

I also cant use the hint (undocumented means ***danger***)

MERGE INTO T
USING (SELECT * FROM S WHERE FLD = 'A') S
  ON (T.VALUE = S.OLD_VALUE)
WHEN MATCHED THEN
UPDATE SET VALUE = OLD_VALUE;

UPDATE allows me to join on VALUE but MERGE dont i get the error.

ERROR at line 3:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "T"."VALUE" 

you wrote
" ... This update will fail if s.new_value is not unique. ..."

not true:
(to continue your last example):

insert into s values('B', 3, 8);
insert into s values('B', 4, 8);

select * from s;

F OLD_VALUE NEW_VALUE
- ---------- ----------
A 1 5
A 2 6
A 3 7
B 3 8
B 4 8

UPDATE T
SET VALUE = (SELECT NEW_VALUE FROM S WHERE VALUE = OLD_VALUE AND FLD = 'A')

join update will fail. You are not doing join update.

Iam sorry that i got the statement wrong actually what i was trying is this.

10:42:31 [SYSADM@AKIVATST]> MERGE INTO T
10:43:03 2 USING (SELECT * FROM S WHERE FLD = 'A') S
10:43:03 3 ON (T.VALUE = S.OLD_VALUE)
10:43:03 4 WHEN MATCHED THEN
10:43:03 5 UPDATE SET VALUE = S.NEW_VALUE;
ON (T.VALUE = S.OLD_VALUE)
*
ERROR at line 3:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "T"."VALUE"

here iam updating value with new_value. There should not be any problem but it still not allowing me.

even for an instance if i want to update with a static value for the matching instance it dose not allows me.

10:46:01 [SYSADM@AKIVATST]> MERGE INTO T
10:46:17 2 USING (SELECT * FROM S WHERE FLD = 'A') S
10:46:17 3 ON (T.VALUE = S.OLD_VALUE)
10:46:17 4 WHEN MATCHED THEN
10:46:17 5 UPDATE SET t.VALUE = 0;
ON (T.VALUE = S.OLD_VALUE)
*
ERROR at line 3:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "T"."VALUE"

You are attempting to update T.VALUE, it doesn't matter what you're trying to update it with, it cannot be updated (since you're using that value to match on).

should i take it for granted. Or it can be given a logical explanation. May be that way we can understand MERGE in a better way. Iam asking this because oracle allows UPDATE statement to do that but not the merge. So just curious to know what stops oracle for allowing it.

SRC:

ID         NEW_ID
------     ---------
5          6
6          7


TGT:

ID
-------
5


Ok, now we merge 

Good point, excellent example.

This is a case where my source and target are the same.

create table t as select level rno, 100*level val from dual connect by level <=10;

Table created.

select * from t;

RNO VAL
---------- ----------
1 100
2 200
3 300
4 400
5 500
6 600
7 700
8 800
9 900
10 1000

10 rows selected.

create unique index t_idx on t(rno);

Index created.

variable row_cnt number

variable key_val number

exec :row_cnt := 10;

PL/SQL procedure successfully completed.

exec :key_val := 5;

PL/SQL procedure successfully completed.

UPDATE t t1
   SET val = (SELECT t2.val
                FROM t t2
               WHERE t2.rno = DECODE(TRUNC((t1.rno + :key_val -1) / :row_cnt), 0, t1.rno + :key_val, (t1.rno + :key_val) -:row_cnt))
 /

10 rows updated.

select * from t;

RNO VAL
---------- ----------
1 600
2 700
3 800
4 900
5 1000
6 100
7 200
8 300
9 400
10 500

10 rows selected.

UPDATE (SELECT trgt.val, src.val new_val
     FROM t trgt, t src
   WHERE DECODE(TRUNC((trgt.rno + :key_val -1) / :row_cnt), 0, trgt.rno + :key_val, (trgt.rno + :key_val) -:row_cnt) = src.rno)
   SET val=new_val
/

set val=new_val
*
ERROR at line 4:
ORA-01779: cannot modify a column which maps to a non key-preserved table


I have a unique index on rno still its not allowing a join update.

On 10g, Merge proves to be very fast than compared to update when the tables have significant size of data.

Here is an example :

create table ttt as select object_id, object_name from all_objects;

select count(*) from ttt;

-------
36561

alter table ttt disable constraint sys_c0074332;

update ttt t
set t.object_name = null;
commit;

create
table sss as select object_id, object_name from all_objects;

No indexes on the tables :
---------------------------

update ttt
set ttt.object_name = (select sss.object_name from sss where sss.object_id = ttt.object_id);

-- took 177.296 seconds

rollback;

merge into ttt t
using (select * from sss) s
on (t.object_id = s.object_id)
when matched then update set t.object_name = s.object_name

-- took 0.907 seconds

rollback;

Create indexes and analyze the tables :
----------------------------------------

create index ttt_idx on ttt(object_id);
create index sss_idx on sss(object_id);

exec dbms_stats.gather_table_stats(ownname => 'idcscd',tabname => 'ttt',method_opt => 'FOR ALL COLUMNS SIZE 1',cascade => true);

exec dbms_stats.gather_table_stats(ownname => 'idcscd',tabname => 'sss',method_opt => 'FOR ALL COLUMNS SIZE 1',cascade => true);

update ttt
set ttt.object_name = (select sss.object_name from sss where sss.object_id = ttt.object_id);

-- took 1.593 seconds

merge into ttt t
using (select * from sss) s
on (t.object_id = s.object_id)
when matched then update set t.object_name = s.object_name

-- took 0.906 seconds

rollback;

So the conclusion is that even with/without indexes Merge performed better than an update.

Hope this helps.

Regards,
Ravi Vedala

<Code>
SQL> 
SQL> CREATE TABLE p (
  2     id           INTEGER NOT NULL,
  3     UserID       INTEGER NOT NULL,
  4     AttributeID  INTEGER NOT NULL,
  5     Value        VARCHAR2(20) NOT NULL,
  6     PRIMARY KEY (id),
  7     UNIQUE (UserID, AttributeID)
  8  );

Table created.

SQL> 
SQL> CREATE TABLE s (
  2     id           INTEGER NOT NULL,
  3     UserID       INTEGER NOT NULL,
  4     AttributeID  INTEGER NOT NULL,
  5     Value        VARCHAR2(20) NOT NULL,
  6     PRIMARY KEY (id),
  7     UNIQUE (UserID, AttributeID)
  8  );

Table created.

SQL> 
SQL> 
SQL> INSERT INTO s (id, UserID, AttributeID, Value) VALUES (1, 1, 1, 'V1');

1 row created.

SQL> INSERT INTO s (id, UserID, AttributeID, Value) VALUES (2, 1, 2, 'V2');

1 row created.

SQL> 
SQL> INSERT INTO p (id, UserID, AttributeID, Value) VALUES (1, 1, 1, 'V0');

1 row created.

SQL> INSERT INTO p (id, UserID, AttributeID, Value) VALUES (2, 1, 2, 'V2');

1 row created.

SQL> 
SQL> COMMIT;

Commit complete.

SQL> 
SQL> -- -------------------------------------------------------------------------
SQL> -- Error: ok. That's fine if that's how it works.
SQL> -- -------------------------------------------------------------------------
SQL> MERGE INTO p
  2  USING s
  3  ON (
  4     p.UserID = s.UserID AND
  5     p.AttributeID = s.AttributeID AND
  6     CASE WHEN LOWER(s.value) = LOWER(p.value) THEN 1 ELSE 2 END = 2
  7  )
  8  WHEN MATCHED THEN
  9     UPDATE SET p.Value = s.Value;
   CASE WHEN LOWER(s.value) = LOWER(p.value) THEN 1 ELSE 2 END = 2
                                    *
ERROR at line 6:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "P"."VALUE"


SQL> 
SQL> -- -------------------------------------------------------------------------
SQL> -- It's great that this works, but why is this ok?
SQL> -- -------------------------------------------------------------------------
SQL> MERGE INTO p
  2  USING s
  3  ON (
  4     p.UserID = s.UserID AND
  5     p.AttributeID = s.AttributeID AND (
  6        CASE WHEN LOWER(s.value) = LOWER(p.value) THEN 1 ELSE 2 END = 2 OR
  7        CASE WHEN LOWER(s.value) = LOWER(p.value) THEN 1 ELSE 2 END = 2
  8     )
  9  )
 10  WHEN MATCHED THEN
 11     UPDATE SET p.Value = s.Value;

1 row merged.

SQL> 
SQL> SELECT * FROM p;

        ID     USERID ATTRIBUTEID VALUE
---------- ---------- ----------- --------------------
         1          1           1 V1
         2          1           2 V2

SQL> SELECT * FROM s;

        ID     USERID ATTRIBUTEID VALUE
---------- ---------- ----------- --------------------
         1          1           1 V1
         2          1           2 V2

</code>

What's the bug? The first MERGE or the second?

What's the reason that Oracle does not allow the first MERGE statement? Isn't it more efficient to only update records that have changed?