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
来源:学生作业帮助网 编辑:作业帮 时间:2024/07/01 06:33:37
![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](/uploads/image/z/10966069-37-9.jpg?t=ORACLE%E5%85%B3%E4%BA%8Emerge+into%E7%94%A8%E6%B3%95%21MERGE+INTO+MSA2008BS.ATYW_ZS_GCZL+AUSING+%28SELECT+%2A+fROM+LOADTMP.MSA2008BS_ATYW_ZS_GCZL+WHERE+ETL_F+IN+%28%27I%27%2C%27D%27%2C%27A%27%29%29+BON+%28A.ATZSCZLOID%3DB.ATZSCZLOID%29WHEN+MATCHED+AND+B.SHIPFID+%3D%27%27+THEN+UPDATE+SETA.SHIPFID+%3DB.SHIPFID+%2CA.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)
收起