Update Query

[sebouh181]

I have 2 tables:
TransHeader Table with fields(ID, TotQty, TotCost)
TransDetail Table with fields(ID, ItemCode, Qty, Cost)

I want to update the TransHeader (TotQty, TotCost) with the sum of (Qty, Cost) from the TransDetail Table, knowing that every TransHeader Table has many TransDetail records.

I want to do this operation with one query.

thanks

[Derftics]

I have 2 tables:
TransHeader Table with fields(ID, TotQty, TotCost)
TransDetail Table with fields(ID, ItemCode, Qty, Cost)

I want to update the TransHeader (TotQty, TotCost) with the sum of (Qty, Cost) from the TransDetail Table, knowing that every TransHeader Table has many TransDetail records.

I want to do this operation with one query.

thanks

Hi i have crated a script for you that you might use for updating. Im not sure if this is what you need cause your info above is incomplete. I have made your column ID as the link to the two tables and I assume that there are many records of QTY and COST in your TransDetail table that belongs to one ID.

select id, sum (Qty)QTY, sum(Cost)COST
into #sum
from TransDetail
group by ID, QTY, Cost

update TransHeader
set TotQty = Qty,
TotCost = Cost
from TransHeader a, #sum b
where a.id = b.id

[sebouh181]

I have these records for example
TransHeader: ID TotQty TotCost
1 - -
TransDet:ID ItemCode Qty Cost
1 1 10 5
1 2 50 25
1 3 40 20

i want to update the TransHeader with one query to become like this
TransHeader: ID TotQty TotCost
1 100 (Sum of the TransDetail.Qty ) 50 (Sum of transDetail.Cos t)

[iburyak]

Try this:


[PHP]Declare @TransHeader table(ID int, TotQty int, TotCost int)

Insert into @TransHeader values(1,0,0)
Insert into @TransHeader values(2,0,0)

Declare @TransDet table (ID int, ItemCode int, Qty int, Cost int)

insert into @TransDet values (1, 1, 10, 5)
insert into @TransDet values (1, 2, 50, 25)
insert into @TransDet values (1, 3, 40, 20)

insert into @TransDet values (2, 1, 70, 15)
insert into @TransDet values (2, 2, 50, 25)
insert into @TransDet values (2, 3, 40, 20)

Update @TransHeader Set
TotQty = (select sum(Qty) from @TransDet where id = h.ID),
TotCost = (select sum(Cost) from @TransDet where id = h.ID)
From @TransHeader h

select * from @TransHeader[/PHP]

Good Luck.

[sebouh181]

I had tried this, it worked fine.
But ther are 2 select statements which is not good for performance.

i just want to know if there is a better way to do so.

thanks for your help

[skumarrcom]

Hi

I think this will help you.

update TransHeader set TotQty = (select sum(Qty) from TransDetail t where t.id=a.id ), TotCost = (select sum(cost) from TransDetail t1 where t1.id=a.id) from TransHeader a

try it

[iburyak]

I had tried this, it worked fine.
But ther are 2 select statements which is not good for performance.

i just want to know if there is a better way to do so.

thanks for your help

Try this:

[PHP]
Declare @TransHeader table(ID int, TotQty int, TotCost int)

Insert into @TransHeader values(1,0,0)
Insert into @TransHeader values(2,0,0)

Declare @TransDet table (ID int, ItemCode int, Qty int, Cost int)

insert into @TransDet values (1, 1, 10, 5)
insert into @TransDet values (1, 2, 50, 25)
insert into @TransDet values (1, 3, 40, 20)

insert into @TransDet values (2, 1, 70, 15)
insert into @TransDet values (2, 2, 50, 25)
insert into @TransDet values (2, 3, 40, 20)

Update @TransHeader Set
TotQty = c.TotQty,
TotCost = c.TotCost
From @TransHeader h
JOIN (select ID, sum(Qty) TotQty, sum(Cost) TotCost
From @TransDet
group by ID) c on h.ID = c.ID

select * from @TransHeader [/PHP]

Good Luck.