KSQL中Update多表级联更新的语法。 收藏
Update XXX Set FXXX =
( Select ①
From ...
Where ②)
Where ③
Update里必须包括3个逻辑部分:
a.) 在①里指明把字段更新为什么值,即新值.
b.) 在②里指明待更新表和源表需要更新行之间的对应关系
c.) 在③里指明待更新表有哪些记录需要更新
注意: 写好updte 语句后需要在ksql中翻译一下,在oracle和db2下可能会多翻译出一些语句(可能包含参数?) ,这样会导致执行时报错:
Caused exception message is: 索引中丢失 IN 或 OUT 参数:: 3
比如:
UPDATE T_SD_SaleOrderEntry
SET (FPreReceived, FUnPrereceivedAmount) =
(SELECT T_SD_SaleOrderEntry.FPreReceived + IsNull(rbe.FActualAmt, 0)
AS FPreReceived,
T_SD_SaleOrderEntry.FPrepayment - IsNull(rbe.FActualAmt, 0)
AS FUnPrereceivedAmount
FROM T_CAS_ReceivingBillEntry rbe
WHERE rbe.FSourceBillEntryId = T_SD_SaleOrderEntry.FID AND rbe.FReceivingBillID = "xxxx")
WHERE T_SD_SaleOrderEntry.FID IN
(SELECT soe.FID
FROM T_SD_SaleOrderEntry soe, T_CAS_ReceivingBillEntry rbe
WHERE rbe.FSourceBillEntryId = soe.FID AND rbe.FReceivingBillID = "xxxx")
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/wiser/archive/2008/10/10/3052172.aspx