使用oracle MERGE INTO 时报错,请大神指点。
直入正题,SQL如下:
MERGE INTO OTC_TRD_SYN_BANKDEAL T
USING (select '28823291' L_SERIAL_NO,
'20170518132918' SYN_BATCH,
'20170517' L_DATE,
'HGR002YH' VC_INTER_CODE,
'2' C_CASH_DIRECTION,
'41201' L_BUSIN_FLAG,
'6' C_ENTRUST_DIRECTION,
'3' L_FUND_ID,
'5' C_MARKET_NO,
'20170517' L_TRADE_DATE,
'20170517' L_SETTLE_DATE,
'265200' EN_SETTLE_BALANCE,
'2652' L_SETTLE_AMOUNT,
'1' C_CASHSETTLE_FLAG,
'1' C_STOCKSETTLE_FLAG,
'1' C_SETTLE_TYPE,
'1' VC_ORIGINAL_NO,
'0' C_CLEAR_SPEED,
'99006' L_TRADE_RIVAL_NO,
'HOLDER5' VC_STOCKHOLDER_ID,
'0' L_PAPER_NO,
'2' C_BUSIN_CLASS,
'23' EN_PRICE,
'1' VC_REMARK,
'0' C_CASH_STATUS,
'27469284' VC_DEAL_NO,
'0' C_INVEST_TYPE,
'0' C_TRUSTEE,
'0' C_SQS_CONTRACT_STATUS,
'0' C_ZZD_CONTRACT_STATUS,
'0' C_SQS_INSTR_STATUS,
'0' C_SQS_CONTRACT_STATUS,
'0' C_STATUS,
'1' C_DISPENSE_STATUS,
'0' C_ENTRUST_EXECUTE_STATUS,
'0' C_DEAL_EXECUTE_STATUS
FROM DUAL) T1
ON (T.L_SERIAL_NO = T1.L_SERIAL_NO)
WHEN MATCHED THEN
UPDATE SET
T.SYN_BATCH = T1.SYN_BATCH,
T.L_DATE = T1.L_DATE,
T.VC_INTER_CODE = T1.VC_INTER_CODE,
T.C_CASH_DIRECTION = T1.C_CASH_DIRECTION,
T.L_BUSIN_FLAG = T1.L_BUSIN_FLAG,
T.C_ENTRUST_DIRECTION = T1.C_ENTRUST_DIRECTION,
T.L_FUND_ID = T1.L_FUND_ID,
T.C_MARKET_NO = T1.C_MARKET_NO,
T.L_TRADE_DATE = T1.L_TRADE_DATE,
T.L_SETTLE_DATE = T1.L_SETTLE_DATE,
T.EN_SETTLE_BALANCE = T1.EN_SETTLE_BALANCE,
T.L_SETTLE_AMOUNT = T1.L_SETTLE_AMOUNT,
T.C_CASHSETTLE_FLAG = T1.C_CASHSETTLE_FLAG,
T.C_STOCKSETTLE_FLAG = T1.C_STOCKSETTLE_FLAG,
T.C_SETTLE_TYPE = T1.C_SETTLE_TYPE,
T.VC_ORIGINAL_NO = T1.VC_ORIGINAL_NO,
T.C_CLEAR_SPEED = T1.C_CLEAR_SPEED,
T.L_TRADE_RIVAL_NO = T1.L_TRADE_RIVAL_NO,
T.VC_STOCKHOLDER_ID = T1.VC_STOCKHOLDER_ID,
T.L_PAPER_NO = T1.L_PAPER_NO,
T.C_BUSIN_CLASS = T1.C_BUSIN_CLASS,
T.EN_PRICE = T1.EN_PRICE,
T.VC_REMARK = T1.VC_REMARK,
T.C_CASH_STATUS = T1.C_CASH_STATUS,
T.VC_DEAL_NO = T1.VC_DEAL_NO,
T.C_INVEST_TYPE = T1.C_INVEST_TYPE,
T.C_TRUSTEE = T1.C_TRUSTEE,
T.C_ZZD_INSTR_STATUS = T1.C_ZZD_INSTR_STATUS,
T.C_ZZD_CONTRACT_STATUS = T1.C_ZZD_CONTRACT_STATUS,
T.C_SQS_INSTR_STATUS = T1.C_SQS_INSTR_STATUS,
T.C_SQS_CONTRACT_STATUS = T1.C_SQS_CONTRACT_STATUS,
T.C_STATUS = T1.C_STATUS,
T.C_DISPENSE_STATUS = T1.C_DISPENSE_STATUS,
T.C_ENTRUST_EXECUTE_STATUS = T1.C_ENTRUST_EXECUTE_STATUS,
T.C_DEAL_EXECUTE_STATUS = T1.C_DEAL_EXECUTE_STATUS
WHEN NOT MATCHED THEN
INSERT(
L_SERIAL_NO,
SYN_BATCH,
L_DATE,
VC_INTER_CODE,
C_CASH_DIRECTION,
L_BUSIN_FLAG,
C_ENTRUST_DIRECTION,
L_FUND_ID,
C_MARKET_NO,
L_TRADE_DATE,
L_SETTLE_DATE,
EN_SETTLE_BALANCE,
L_SETTLE_AMOUNT,
C_CASHSETTLE_FLAG,
C_STOCKSETTLE_FLAG,
C_SETTLE_TYPE,
VC_ORIGINAL_NO,
C_CLEAR_SPEED,
L_TRADE_RIVAL_NO,
VC_STOCKHOLDER_ID,
L_PAPER_NO,
C_BUSIN_CLASS,
EN_PRICE,
VC_REMARK,
C_CASH_STATUS,
VC_DEAL_NO,
C_INVEST_TYPE,
C_TRUSTEE,
C_ZZD_INSTR_STATUS,
C_ZZD_CONTRACT_STATUS,
C_SQS_INSTR_STATUS,
C_SQS_CONTRACT_STATUS,
C_STATUS,
C_DISPENSE_STATUS,
C_ENTRUST_EXECUTE_STATUS,
C_DEAL_EXECUTE_STATUS)
VALUES(
T1.L_SERIAL_NO,
T1.SYN_BATCH,
T1.L_DATE,
T1.VC_INTER_CODE,
T1.C_CASH_DIRECTION,
T1.L_BUSIN_FLAG,
T1.C_ENTRUST_DIRECTION,
T1.L_FUND_ID,
T1.C_MARKET_NO,
T1.L_TRADE_DATE,
T1.L_SETTLE_DATE,
T1.EN_SETTLE_BALANCE,
T1.L_SETTLE_AMOUNT,
T1.C_CASHSETTLE_FLAG,
T1.C_STOCKSETTLE_FLAG,
T1.C_SETTLE_TYPE,
T1.VC_ORIGINAL_NO,
T1.C_CLEAR_SPEED,
T1.L_TRADE_RIVAL_NO,
T1.VC_STOCKHOLDER_ID,
T1.L_PAPER_NO,
T1.C_BUSIN_CLASS,
T1.EN_PRICE,
T1.VC_REMARK,
T1.C_CASH_STATUS,
T1.VC_DEAL_NO,
T1.C_INVEST_TYPE,
T1.C_TRUSTEE,
T1.C_ZZD_INSTR_STATUS,
T1.C_ZZD_CONTRACT_STATUS,
T1.C_SQS_INSTR_STATUS,
T1.C_SQS_CONTRACT_STATUS,
T1.C_STATUS,
T1.C_DISPENSE_STATUS,
T1.C_ENTRUST_EXECUTE_STATUS,
T1.C_DEAL_EXECUTE_STATUS);
在数据库工具中执行报错如下:
命令出错, 行: 40 列: 1
错误报告:
SQL 错误: ORA-00918: 未明确定义列
00918. 00000 - "column ambiguously defined"
*Cause:
*Action:
第40行是 WHEN MATCHED THEN