[K/3Cloud] KSQL 关联表更新字段Update语法

关联表更新字段
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://my.oschina.net/fanyongqiang/blog/654396

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 扫一扫,分享海报

参与评论
请先登录 后发表评论~
©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值