[K/3Cloud] KSQL 关联表更新字段Update语法-优快云博客

本文详细介绍了SQL中复杂的更新操作，包括使用子查询更新字段、更新多条记录的方法及注意事项等。通过具体实例展示了如何在不同场景下进行数据更新。

关联表更新字段

UPDATE tmp369faa3f7d224b0595670425008 as t1 SET FStatus=-1  
where exists(select 1 from t_BD_Supplier where FUseOrgId = t1.FDestOrgID and FMasterId = t1.FMasterId)

UPDATE 后面使用别名必须加AS；

另一种写法：

update t_pm_otherowner set fcontrolunitid=(select fcontrolunitid from t_pm_specialdataperm where fid=fspecialdatapermid);

复杂更新语句：

UPDATE T_SD_SaleContractEntry
SET (FTotalOrderedAmt, FTotalOrderedQty) = 
(SELECT temp.FTotalOrderedAmt, temp.FTotalOrderedQty  FROM TABLE temp 
WHERE EXISTS(SELECT 1 FROM IDTABLE idtemp WHERE idtemp.FEntryID=temp.FSourceBillEntryId AND idtemp.FDstID IN (?)) AND T_SD_SaleContractEntry.FID=temp.FSourceBillEntryId)

更新多条记录：
Update 后不要使用别名更新。

UPDATE T0 SET (FYEAR) = 
(SELECT TO_INT(T1.FVALUE) FROM tmp0dd8fdf5aa1e4ebdad2a04d9266 T0 INNER JOIN T_BAS_SYSTEMPROFILE T1 ON T0.FBOOKID = T1.FACCOUNTBOOKID   WHERE T1.FCategory = 'GL' AND T1.FORGID = 0 AND T1.FKey = 'CurrentYear')

在Oracle下提示T0不存在，应写为：

UPDATE tmp55ff93c1200244a8848291ed180 SET (FYEAR) = ( SELECT T1.FVALUE FROM T_BAS_SYSTEMPROFILE T1 WHERE T1.FCategory = 'GL' AND T1.FORGID = 0 AND T1.FKey = 'CurrentYear' AND tmp55ff93c1200244a8848291ed180.FBOOKID = T1.FACCOUNTBOOKID)

转载于:https://www.cnblogs.com/fyq891014/p/3308529.html