使用来自另一个表的数据进行一些计算来更新一个表的多行. [英] Update multiple rows of a table with some calculation done with data from another table .

问题描述

我有3张桌子，我的要求是参考另一个桌子来更新一个桌子的一些记录(使用数学公式).
样品表和样品数据如下:-

筛选的<表格边框="1"> ECode CountyGroupname 项目名称 工作类型 持续时间 101 NY Proj1 WT1 10 101 NY Proj2 WT2 20 102 NP Proj1 WT1 20 101 NP Proj3 WT3 10 102 NK Proj1 WT1 20


NonProd <表格边框="1"> ECode CountyGroupName 持续时间 101 NY 10 102 NP 10 103 NY 10


Prod <表border ="1"> ECode CountyGroupname 持续时间 101 NY 30 101 NP 35 102 ND 35 103 NY 40 104 GP 45

我要根据以下条件将过滤的"表列名称更新为持续时间":-
更新已过滤
设置Duration =(公式将是:-Filtered.Duration * NonProd.Duration)/Prod.Duration when
条件1:Filtered.CountyGroupname = NonProd.CountyGroupname = Prod.CountyGroupName
条件2:Filtered.Ecode = NonProd.Ecode = Prod.ECode
哪里存在
()

注意:-按照逻辑，只有过滤表"中持续时间的前3行得到了更新.其余字段将保持不变.

I have 3 table and my requirement is to update few records of a table(Using mathematical formula) with refence of the another table.
Sample table and sample data are as below:-

Filtered

ECode	CountyGroupname	Projectname	Worktype	Duration
101	NY	Proj1	WT1	10
101	NY	Proj2	WT2	20
102	NP	Proj1	WT1	20
101	NP	Proj3	WT3	10
102	NK	Proj1	WT1	20

NonProd

ECode	CountyGroupName	Duration
101	NY	10
102	NP	10
103	NY	10

Prod

ECode	CountyGroupname	Duration
101	NY	30
101	NP	35
102	ND	35
103	NY	40
104	GP	45

I want to update the "Filtered" Table Column Name is "Duration" as per the condition that:-
Update Filtered
Set Duration= (Formula will be:- Filtered.Duration * NonProd.Duration)/Prod.Duration when
Condition 1: Filtered.CountyGroupname=NonProd.CountyGroupname=Prod.CountyGroupName
Condition 2: Filtered.Ecode=NonProd.Ecode=Prod.ECode
where exists
()

Note:- As per the logic only first 3 Rows Of the duration in "Filtered Table" got updated.Rest of the field will not be changed.

推荐答案

您好，

将更新与联接一起使用

http://geekswithblogs.net/faizanahmad/archive/2009 /01/05/join-in-sql-update--statement.aspx [

Hi,

use update with joins

http://geekswithblogs.net/faizanahmad/archive/2009/01/05/join-in-sql-update--statement.aspx[^]

Hope this helps.

您可以通过查询来执行此操作，但是如果您使用存储过程，那就麻烦了，因为您必须使用联接和子查询来创建非常复杂的查询.

U can do this by query but it is batter if u use stored procedure because u have to use join and sub-query which create query very complex.

这篇关于使用来自另一个表的数据进行一些计算来更新一个表的多行.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！