Oracle sequence increased unexpected when using in merge statement
来源:互联网 发布:艾丁格啤酒价格知乎 编辑:程序博客网 时间:2024/05/29 14:29
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, name varchar2(10), 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, 'BBB',5);
insert into tab2 values(2, 'CCC',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', 'EEE', '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, T.name, T.score) VALUES (seq.nextval, F.name, 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, T.name, T.score) VALUES (seq.nextval, F.name, F.score);
- Oracle sequence increased unexpected when using in merge statement
- oracle中merge into..using..on..when..when..用法
- oracle中merge into..using..on..when..when..用法
- The merge statement in oracle 9i
- oracle中merge into..using..on..when..when..用法 使用一条语句实现update/insert 语句
- trap or bug when using CONTINUE in Oracle 11g
- Oracle SQL 1 : Oracle MERGE INTO Statement
- Understanding the 'using' statement in C#
- oracle merge into using 实例
- Oracle语法:merge into using
- [MSSQL]SQL Server 2008 Using the MERGE statement
- Sequence in Oracle DB
- Unexpected error: Invalid byte sequence in conversion input的问题
- Unexpected error: Invalid byte sequence in conversion input的问题
- MERGE INTO USING ON WHEN MATCHED THEN WHEN NOT MATCHED THEN
- Using $this when not in object context in
- Using $this when not in object context in
- Oracle merge into using 的用法
- Python聚类工具scipy cluster
- 机器学习算法 -- PCA 学习笔记
- Maven 那点事儿
- 自律,自由。
- 基于opencV的动态背景下运动目标检测及跟踪(修改版)
- Oracle sequence increased unexpected when using in merge statement
- 微博市值反超Twitter成市值最高社交媒体股 一下科技强力助攻
- c语言之简单四则运算器
- spacemacs升级步骤
- python小脚本 删除含有特定字符的txt,并重命名
- [互联网思维(转)]互联网思维法则
- 跳跃表-原理及Java实现
- 网关 DNS
- hibernate spatial - 保存oracle具有空间字段的表