ORACLE关于merge into用法!MERGE INTO MSA2008BS.ATYW_ZS_GCZL AUSING (SELECT * fROM LOADTMP.MSA2008BS_ATYW_ZS_GCZL WHERE ETL_F IN ('I','D','A')) BON (A.ATZSCZLOID=B.ATZSCZLOID)WHEN MATCHED AND B.SHIPFID ='' THEN UPDATE SETA.SHIPFID =B.SHIPFID ,A.GCZ

ORACLE关于merge into用法!MERGE INTO MSA2008BS.ATYW_ZS_GCZL AUSING (SELECT * fROM LOADTMP.MSA2008BS_ATYW_ZS_GCZL WHERE ETL_F IN ('I','D','A')) BON (A.ATZSCZLOID=B.ATZSCZLOID)WHEN MATCHED AND B.SHIPFID ='' THEN UPDATE SETA.SHIPFID =B.SHIPFID ,A.GCZ
ORACLE关于merge into用法!
MERGE INTO MSA2008BS.ATYW_ZS_GCZL A
USING (SELECT * fROM LOADTMP.MSA2008BS_ATYW_ZS_GCZL WHERE ETL_F IN ('I','D','A')) B
ON (A.ATZSCZLOID=B.ATZSCZLOID)
WHEN MATCHED AND B.SHIPFID ='' THEN UPDATE SET
A.SHIPFID =B.SHIPFID ,
A.GCZLDJZHM=B.GCZLDJZHM ,
A.CBDJH =B.CBDJH
WHEN NOT MATCHED THEN
INSERT(A.ATZSCZLOID,A.SHIPFID,A.GCZLDJZHM,A.CBDJH,A.CBCZR)
VALUES(B.ATZSCZLOID,B.SHIPFID,B.GCZLDJZHM,B.CBDJH,B.CBCZR)
;
中因为在WHEN MATCHED后加入了AND条件即：AND B.SHIPFID =''
之后报错,是不是oracle不支持WHEN MATCHED后跟条件呢?

ORACLE关于merge into用法!MERGE INTO MSA2008BS.ATYW_ZS_GCZL AUSING (SELECT * fROM LOADTMP.MSA2008BS_ATYW_ZS_GCZL WHERE ETL_F IN ('I','D','A')) BON (A.ATZSCZLOID=B.ATZSCZLOID)WHEN MATCHED AND B.SHIPFID ='' THEN UPDATE SETA.SHIPFID =B.SHIPFID ,A.GCZ
在when matched then 和 when not matched then 后面只能加insert() values 或者 update set .,. ,不能附加条件,而且then 后面的语句不能使用其他语句. 另外注意到,B的域只在using里面的查询有用吧 ,如果你要使用 也得是 在Using （） M 用m吧

WHERE只能跟在数据库对象之后，比如表和视图

qiyechao
说的很对，when matched then 和 when not matched then 后面只能加insert() values 或者 update set ，B的域只在using里面的查询有用，AND B.SHIPFID ='' 写在using里面,即
MERGE INTO MSA2008BS.ATYW_ZS_GCZL A
USING (SELE...

全部展开

qiyechao
说的很对，when matched then 和 when not matched then 后面只能加insert() values 或者 update set ，B的域只在using里面的查询有用，AND B.SHIPFID ='' 写在using里面,即
MERGE INTO MSA2008BS.ATYW_ZS_GCZL A
USING (SELECT * fROM LOADTMP.MSA2008BS_ATYW_ZS_GCZL WHERE ETL_F IN ('I','D','A') AND B.SHIPFID ='' ) B
ON (A.ATZSCZLOID=B.ATZSCZLOID)
WHEN MATCHED THEN UPDATE SET
A.SHIPFID =B.SHIPFID ,
A.GCZLDJZHM=B.GCZLDJZHM ,
A.CBDJH =B.CBDJH
WHEN NOT MATCHED THEN
INSERT(A.ATZSCZLOID,A.SHIPFID,A.GCZLDJZHM,A.CBDJH,A.CBCZR)
VALUES(B.ATZSCZLOID,B.SHIPFID,B.GCZLDJZHM,B.CBDJH,B.CBCZR)

收起