April 21, 2008 at 7:40 am
Greetings,
I have a table containing 40,000 records. There are 4 cost fields for each record. I am attempting to update the cost information for each record at the same time through an update statement that links to another table and sums the costs for each field for each record. I am linking to the other table on the main unique identification field. However, all 40,000 records are updated with the same information? I have done many update statements however not one that sums or one that tries to update each row uniquely. What am I missing?
Here is the statement:
Update #str
set Equipment = ISNULL((select sum(Extended_Cost)
from #str join COSTS on #str.ID = COSTS.ID
where #str.ID = COSTS.ID and #str.CUST = COSTS.CUST and
CCode= '1' ), 0),
Labor = ISNULL((select sum(Extended_Cost)
from #str left join COSTS on #str.ID = COSTS.ID
where CCode= '6' ), 0),
Material = ISNULL((select sum(Extended_Cost)
from #str left join COSTS on #str.ID = COSTS.ID
where CCode= '2' ), 0),
Subs = ISNULL((select sum(Extended_Cost)
from #str left join COSTS on #str.ID = COSTS.ID
where CCode= '4' ), 0),
Other = ISNULL((select sum(Extended_Cost)
from #str left join COSTS on #str.ID = COSTS.ID
where CCode= '5' ), 0),
Cost_All = ISNULL((select sum(Extended_Cost)
from #str left join COSTS on #str.ID = COSTS.ID
/*where #str.ID = '080204-0363'*/), 0),
Billable_All_No_Tax = ISNULL((select sum(Billing_Amount)
from #str left join COSTS on #str.ID = COSTS.ID
/*where #str.ID = '080204-0363'*/), 0),
Billable_Labor_Total = ISNULL((select sum(Billing_Amount)
from #str left join COSTS on #str.ID = COSTS.ID
where CCode= '6' ), 0)--,
from #str left join COSTS on #str.ID = COSTS.ID
Thank you for your assistance 🙂
"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec
April 21, 2008 at 7:43 am
The query you will need will be much less complicated than this. If you post your table structure, and some sample data, it will be much easier and quicker for us to help you... 🙂
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 21, 2008 at 7:56 am
This would be a guess in the absence of more information. To put this together, I first focused on getting the data we needed from the COSTS table and in the format we need it (this is 'b' - which you can run on its own should you wish). Then, I joined it to the #str table to do the update.
update a set
Equipment = b.Equipment,
Material = b.Material,
Subs = b.Subs,
Other = b.Other,
Labor = b.Labor,
Billable_Labor_Total = b.Billable_Labor_Total,
Billable_All_No_Tax = b.Billable_All_No_Tax
from #str a inner join (
select
sum(case when CCode = '1' then Extended_Cost else 0 end) as Equipment,
sum(case when CCode = '2' then Extended_Cost else 0 end) as Material,
sum(case when CCode = '4' then Extended_Cost else 0 end) as Subs,
sum(case when CCode = '5' then Extended_Cost else 0 end) as Other,
sum(case when CCode = '6' then Extended_Cost else 0 end) as Labor,
sum(case when CCode = '6' then Billing_Amount else 0 end) as Billable_Labor_Total,
sum(Billing_Amount) as Billable_All_No_Tax
from COSTS group by ID) b on a.ID = b.ID
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 21, 2008 at 8:28 am
Ryan,
Thank you for your assistance. Mine was obviously extremely inefficient. I tried your solution and got the following error:
Invalid column name 'ID'
Both tables have an ID column so I am unsure why the error?
"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec
April 21, 2008 at 8:35 am
Ryan-
I added the ID to the select statement and it works. Thank you for your assistance! 😀
update a set
Equipment = b.Equipment,
Material = b.Material,
Subs = b.Subs,
Other = b.Other,
Labor = b.Labor,
Billable_Labor_Total = b.Billable_Labor_Total,
Billable_All_No_Tax = b.Billable_All_No_Tax
from #str a inner join (
select
ID,
sum(case when CCode = '1' then Extended_Cost else 0 end) as Equipment,
sum(case when CCode = '2' then Extended_Cost else 0 end) as Material,
sum(case when CCode = '4' then Extended_Cost else 0 end) as Subs,
sum(case when CCode = '5' then Extended_Cost else 0 end) as Other,
sum(case when CCode = '6' then Extended_Cost else 0 end) as Labor,
sum(case when CCode = '6' then Billing_Amount else 0 end) as Billable_Labor_Total,
sum(Billing_Amount) as Billable_All_No_Tax
from COSTS group by ID) b on a.ID = b.ID
"Some like to understand what they believe in. Others like to believe in what they understand."
--Stanislaus J. Lec
April 21, 2008 at 8:42 am
Oops - yep I missed the ID :pinch: - glad I could help though 🙂
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply