sql 中根据多表之间的条件删除和更新记录的示例

最新推荐文章于 2023-03-02 00:44:13 发布

hz_yihang

最新推荐文章于 2023-03-02 00:44:13 发布

阅读量895

点赞数

分类专栏： SQL SERVER 文章标签： sql delete

本文链接：https://blog.csdn.net/hz_yihang/article/details/2420722

版权

SQL SERVER 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

--更新记录

Update t1 SET t1.FAuxQty=t1.FAuxQty+t2.FAuxQty, t1.FSecQty=t1.FSecQty+t2.FSecQty,
t1.FAmount=t1.FAmount+t2.FAmount, t1.FPlanAmount=t1.FPlanAmount+t2.FPlanAmount,
t1.FAuxPrice=CASE WHEN (t1.FAuxQty+t2.FAuxQty)<>0 then (t1.FAmount+t2.FAmount)/(t1.FAuxQty+t2.FAuxQty) ELSE t1.FAuxPrice END,
t1.FProcessPrice=CASE WHEN (t1.FAuxQty+t2.FAuxQty)<>0 then (t1.FProcessCost+t2.FProcessCost)/(t1.FAuxQty+t2.FAuxQty) ELSE t1.FProcessPrice END,
t1.FConsignAmount=t1.FConsignAmount+t2.FConsignAmount,
t1.FMaterialCost=t1.FMaterialCost+t2.FMaterialCost,
t1.FProcessCost=t1.FProcessCost+t2.FProcessCost,
t1.FTaxAmount=t1.FTaxAmount+t2.FTaxAmount,
t1.FConsignPrice=CASE WHEN (t1.FAuxQty+t2.FAuxQty)<>0 then (t1.FConsignAmount+t2.FConsignAmount)/(t1.FAuxQty+t2.FAuxQty) ELSE t1.FConsignPrice END
FROM ICStockBillEntry t1,#CombineBillEntry t2
WHERE t1.FInterID=t2.FInterID AND t1.FOrgBillEntryID=t2.FOrgBillEntryID AND t1.FItemID=t2.FItemID

--删除记录
DELETE t1 FROM #CombineBillEntry t1,ICStockBillEntry t2 WHERE t1.FInterID=t2.FInterID AND t1.FOrgBillEntryID=t2.FOrgBillEntryID AND t1.FItemID=t2.FItemID