MERGE INTO A --表名 USING (SELECT 字段1,字段2 FROM b , merger_subquery ON (表.主键 = merger_subquery.主键) --条件 WHEN NOT MATCHED THEN INSERT (字段1 , 字段2 ) VALUES (merger_subquery.字段1 , merger_subquery.字段2 ) WHEN MATCHED THEN UPDATE SET COMMIT;
merge into FX_AGENCY L using (select 2 AGENCYID from dual) N on (L.AGENCYID=N.AGENCYID) WHEN MATCHED THEN UPDATE set AGENCYID=1 WHEN NOT MATCHED THEN INSERT (AGENCYID,AGENCYNAME) values (1,'1')
ORA-38104: 无法更新 ON 子句中引用的列: "L"."AGENCYID"提示有错误,也没发现有什么问题呀
merge into FX_AGENCY L using (select 2 AGENCYID from dual) N on (L.AGENCYID=N.AGENCYID) WHEN MATCHED THEN INSERT (AGENCYID,AGENCYNAME) values (1,'1') WHEN NOT MATCHED THEN INSERT (AGENCYID,AGENCYNAME) values (1,'1')
when matched then 后边不能再insert 改成update语句 select 2 AGENCYID from dual 把2加上引号 select '2' AGENCYID from dual
merge into 做不了 是不能更新 on里面的关联列 你写个过程吧
不知道这个问题解决没有,我现在也碰到这个问题了。MERGE INTO sup.cfg_price_template pt USING (select 6 as temp_id, 2115 as ware_id, 205 as id, 380 as price from dual) t2 ON (pt.temp_id = t2.temp_id) WHEN MATCHED THEN UPDATE SET pt.price = 235
WHERE pt.ware_id = 2115 and pt.temp_id = 6 WHEN NOT MATCHED THEN INSERT (ID, TEMP_ID, WARE_ID, PRICE) VALUES (t2.id, t2.temp_id, t2.ware_id, t2.price)可以更新(update)但是不能插入
USING (SELECT 字段1,字段2 FROM b ,
merger_subquery
ON (表.主键 = merger_subquery.主键) --条件
WHEN NOT MATCHED THEN
INSERT
(字段1 ,
字段2 )
VALUES
(merger_subquery.字段1 ,
merger_subquery.字段2
)
WHEN MATCHED THEN
UPDATE
SET COMMIT;
merge into FX_AGENCY L
using (select 2 AGENCYID from dual) N
on (L.AGENCYID=N.AGENCYID)
WHEN MATCHED THEN
UPDATE set AGENCYID=1
WHEN NOT MATCHED THEN
INSERT (AGENCYID,AGENCYNAME) values (1,'1')
using (select 2 AGENCYID from dual) N
on (L.AGENCYID=N.AGENCYID)
WHEN MATCHED THEN
INSERT (AGENCYID,AGENCYNAME) values (1,'1')
WHEN NOT MATCHED THEN
INSERT (AGENCYID,AGENCYNAME) values (1,'1')
ORA-00905: 缺失关键字
如果有则将AGENCYID改为1
如果没有则插入数据?
select 2 AGENCYID from dual 把2加上引号 select '2' AGENCYID from dual
你写个过程吧
USING (select 6 as temp_id, 2115 as ware_id, 205 as id, 380 as price
from dual) t2
ON (pt.temp_id = t2.temp_id)
WHEN MATCHED THEN
UPDATE
SET pt.price = 235
WHERE pt.ware_id = 2115
and pt.temp_id = 6
WHEN NOT MATCHED THEN
INSERT
(ID, TEMP_ID, WARE_ID, PRICE)
VALUES
(t2.id, t2.temp_id, t2.ware_id, t2.price)可以更新(update)但是不能插入