Following sample is going to merge from tab2 to tab1:

CREATE TABLE tab1(id NUMBER,name varchar2(10),score NUMBER);

CREATE TABLE tab2(id NUMBER,score NUMBER);

CREATE SEQUENCE seq START WITH 11 INCREMENT BY 1 NOCACHE NOCYCLE;

insert into tab1 values(1,'AAA',10);

insert into tab1 values(2,'BBB',20);

insert into tab1 values(3,'CCC',30);

insert into tab2 values(1,5);

insert into tab2 values(2,5);

insert into tab2 values(3,'DDD',40);

insert into tab2 values(4,'EEE',50);

insert into tab2 values(5,'FFF',60);

MERGE INTO tab1 T USING tab2 F

ON (F.name = T.name)

WHEN MATCHED THEN

UPDATE SET T.score = F.score + T.score

WHEN NOT MATCHED THEN

INSERT (T.id,T.name,T.score) VALUES (seq.nextval,F.name,F.score);

-- tab1 has 3 records

-- tab2 has 5 records

-- the merge operation wish 3 records('DDD','FFF') are added into tab1,and 2 records('BBB','CCC') are updated to tab1.

-- the next value of sequence seq is 11.

-- so the expected result is:

ID NAME score

---------- ---------- ----------

1 AAA 10

2 BBB 25

3 CCC 35

11 FFF 60

12 EEE 50

13 DDD 40

-- however,the real result is:

ID NAME score

---------- ---------- ----------

1 AAA 10

2 BBB 25

3 CCC 35

13 FFF 60

14 EEE 50

15 DDD 40

-- the problem is the id field (sequence) is not as expected; 11 is the beginning value as expected,but 13 is the real beginning value.

This is because the update branch also occupied a sequence value,see:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns002.htm#sthref809

· For each row merged by a MERGE statement. The reference to NEXTVAL can appear in the merge_insert_clause or the merge_update_clause or both. The NEXTVALUE value is incremented for each row updated and for each row inserted,even if the sequence number is not actually used in the update or insert operation. If NEXTVAL is specified more than once in any of these locations,then the sequence is incremented once for each row and returns the same value for all occurrences of NEXTVAL for that row.

Solution 1: separate the insert and update into 2 merge statements.

MERGE INTO tab1 T USING (select * from tab2 where name in (select name from tab1)) F

ON (F.name = T.name)

WHEN MATCHED THEN

UPDATE SET T.score = F.score + T.score;

-- WHEN NOT MATCHED THEN

-- INSERT (T.id,F.score);

MERGE INTO tab1 T USING (select * from tab2 where name not in (select name from tab1)) F

ON (F.name = T.name)

-- WHEN MATCHED THEN

-- UPDATE SET T.score = F.score + T.score;

WHEN NOT MATCHED THEN

INSERT (T.id,F.score);