由于连接查询时可能会出现一条对应多条的情况:
如A表与B表连接查询,A.id有可能对应B.id多个结果,如果这样写:
update
(
select A.id as var,B.id as value
from A,B where A.key = B.nokey
)
set var=value;
如果在B表的nokey字段上建一个唯一索引,我猜想应该能行(有时间下去试试)
不多说了,这里给我实际工作中用到的一个例子:
update
(select
/*+ BYPASS_UJVC */ --跳过检查(使用这个就可跳过检查)
FP.PContractNo as PContractNo, --合同号
BC.SerialNo as ContractNo,
FP.PCertID as PCertID, --证件号
CI.CertID as CertID,
FP.PLoanType as PLoanType, --贷款类型(业务品种)
BC.BusinessType as LoanType,
CR.ObjectNo as ObjectNo,
FP.PRepayDate as PRepayDate, --扣款日期
BC.MonRepayDay as RepayDate,
FP.PInstmentType as PInstmentType, --扣款类型
BC.ReturnPeriod as InstmentType
from FUND_PRETREAT FP,BUSINESS_CONTRACT BC,CONTRACT_RELATIVE CR,CUSTOMER_INFO CI
where FP.ContractNo = BC.SerialNo
and BC.SerialNo = CR.ObjectNo
and CR.ObjectType = 'AF'
and BC.CustomerID = CI.CustomerID
and BC.InputOrgId in (select BelongOrgId from Org_Belong where OrgId = '?')
)
set PContractNo = ContractNo,
PCertID = CertID,
PLoanType = decode(ObjectNo,null,decode(LoanType,'91','01','92','01','02'),decode(LoanType,'91','03','92','03','04')),
PRepayDate = RepayDate,
PInstmentType = InstmentType
当然你得保证一个更新的字段是一对一的,否则说不定会出现什么问题。