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
6 2851
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
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.Cost)
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.
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
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
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Surendra |
last post by:
I have this query that I need to use in an Update statement to
populate a field in the table by the value of Sq
----------------------------------------------------------------------------
Inline...
|
by: Mike Leahy |
last post by:
Hello all,
This question is related to updating tables - is there any way to calculate or
update the values in a column in a table to the values in a field produced by
a query result? An...
|
by: Randy Harris |
last post by:
I imported records into a table, later found out that many of them had
trailing spaces in one of the fields. If I'd caught it sooner, I could have
trimmed the spaces before the import.
This...
|
by: deko |
last post by:
I'm trying to update the address record of an existing record in my mdb with
values from another existing record in the same table. In pseudo code it
might look like this:
UPDATE tblAddress SET...
|
by: Maxi |
last post by:
There is a lotto system which picks 21 numbers every day out of 80
numbers.
I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21)
Here is the structure and sample data:
...
|
by: Mark Carlyle via AccessMonster.com |
last post by:
I have this update query that I am trying to run. I know the syntax is
messed up but do not know how to correct it.
Select 'UPDATE', Transactions,'Set = where =
' From "Get Daily Balances"
...
|
by: Shapper |
last post by:
Hello,
I have created 3 functions to insert, update and delete an Access
database record. The Insert and the Delete code are working fine.
The update is not. I checked and my database has all...
|
by: Lag |
last post by:
Having a problem updating my database from a web page, through a
submission form. Can anyone help?
----THIS IS MY CODE IN update.php----(user, pass, and database are
typed in directly, I...
|
by: eholz1 |
last post by:
Hello PHP programmers.
I had a brilliant idea on one of my pages that selects some data from
my mysql database.
I first set the page up to display some info and an image, just one
item, with a...
|
by: ARC |
last post by:
Hello all,
So I'm knee deep in this import utility program, and am coming up with all
sorts of "gotcha's!".
1st off. On a "Find Duplicates Query", does anyone have a good solution for...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| |