刚开始接触merge into 使用上出现了问题,希望大家帮忙!
有一表,结构如下
create table LOT
(
LOT_SN VARCHAR2(100) not null,
SALE_TIME DATE not null,
STAF_NUM NUMBER not null,
DRC_ID NUMBER not null,
STAF_DETAILS VARCHAR2(200),
LOAD_TIME DATE default sysdate,
CHILD_ID NUMBER default 0,
MULTI_NUM NUMBER default 1
)
主键是 LOT_SN,STAF_NUM,DRC_ID
LOT_OLD与LOT结构一样,现在把LOT_OLD中的数据用下面merge into到LOT中
merge into lot c
using (select * from lot_old) cu
on (c.lot_sn = cu.lot_sn)
when matched then
update set c.drc_id = cu.drc_id
when not matched then
INSERT
(LOT_SN,
SALE_TIME,
STAF_NUM,
DRC_ID,
STAF_DETAILS,
LOAD_TIME,
CHILD_ID,
MULTI_NUM)
VALUES
(cu.LOT_SN,
cu.SALE_TIME,
cu.STAF_NUM,
cu.DRC_ID,
cu.STAF_DETAILS,
cu.LOAD_TIME,
cu.CHILD_ID,
cu.MULTI_NUM);
问题就是,我有多条LOT_SN相同的数据,比如两条,那么我应该第二条的LOT_SN满足了match条件,应该是执行update,但是多条数据都insert了,这是什么回事呢?

最新回复
jack_wyx (2008-6-25 14:21:45)
lypch (2008-7-10 17:57:34)
a2570 (2008-7-11 16:18:45)
INSERT INTO LOT
SELECT FCTR.LOT_SN,
FCTR.SALE_TIME,
FCTR.STAF_NUM,
FCTR.DRC_ID,
FCTR.STAF_DETAILS,
FCTR.LOAD_TIME,
FCTR.CHILD_ID,
FCTR.MULTI_NUM
FROM (SELECT LOT_OLD.*,
ROW_NUMBER() OVER(PARTITION BY LOT_SN ORDER BY LOAD_TIME DESC) RN
FROM LOT_OLD CU
WHERE NOT EXISTS (SELECT 1 FROM LOT WHERE LOT.LOT_SN = CU.LOT_SN)) FCTR
WHERE FCTR.RN = 1;
jack_wyx (2008-7-24 14:47:26)
那么既是说,merge into 并不是简单的符合ON后面条件的存在就update,不存在就insert,是不是可以理解为他本身含有隐藏条件,要同时满足ON和隐藏条件的存在才会update,否则就是insert呢?