how to use merge into
409605Nov 21 2003 — edited Jan 18 2011my sql scripts:
------------------------------
create table test1 ( a int );
insert into test1 values(1);
insert into test1 values(2);
insert into test1 values(3);
insert into test1 values(4);
insert into test1 values(5);
commit;
create table test2 (b int );
insert into test2(b) select a from test1 where a<4;
insert into test2 values(9);
commit;
merge into test2 t2
using (select a from test1) t1
on (t2.b=t1.a)
when matched then update set t2.b=t1.a+1
when not matched then insert (b) values (t1.a);
-------------------------
but the sqlplus show errors:
ERROR on 3 line:
ORA-38104: Columns referenced in the ON Clause cannot be updated: "T2"."B"
what can I do ?
thanks advanced.