SQL Syntax update totals

I have a situation where I need to update header records based on values in detail records.
In this case there are  PO header and detail tables.
POHEADER.PONUMBER
POHEADER.TOTAL

The detail table has
PODETAIL.PONUMBER
PODETAIL.POLINE
PODETAIL.QUANTITY
PODETAIL.UNITCOST

I need to do a mass update of the POHEADER.TOTAL field based on the sum of the details lines on the corrresponding PO.  So if PO number 1000 has two lines with quantities of 2 and 4 and costs of $50 and $100, respectively. The total cost based on the detail is 2 x 50 and 4 x 100 for a total of $100 + $400 = $500. I need POHEADER.TOTAL to equal $500
rwheeler23Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
didnthaveanameConnect With a Mentor Commented:
You can also try:

update POHEADER
set TOTAL = PD.TOTAL 
from
	POHEADER as PH
	inner join
	(
		select
			PONUMBER,
			sum( quantity * unitCost ) as TOTAL
		from
			PODETAIL
		group by
		  PONUMBER
	) as PD on PH.PONUMBER = PD.PONUMBER;

Open in new window


I like this question =)  Hope it helps!

If anyone wants to play with this: http://sqlfiddle.com/#!3/d5f8d/1/0
0
 
tsnironeConnect With a Mentor Commented:
Been quite a few years since I've dwelved into sql but I think this should work:

select PONUMBER, sum(UNITCOST)*QUANTITY SUMA
from PODETAIL
into #tmp1
group by PONUMBER
go
update POHEADER
from POHEADER A, #tmp1 B
set A.TOTAL=B.SUMA
where A.PONUMBER = B.PONUMBER
go

HTH :)
0
 
rwheeler23Author Commented:
That SQL Fiddle thing is cool. I did find that this works too! Thanks for the tips.

update POHEADER
set SUBTOTAL= (select sum(extdcost) from PODETAIL where ponumber=p2.ponumber)
from POHEADER p2
where (select sum(extdcost) from PODETAIL where ponumber=p2.ponumber)  is not null

Apparently there were some PO Header line that had no matching PO Detail lines hence the check for NULL.
0
 
PaulConnect With a Mentor Commented:
from a query efficiency standpoint, doing one summing subquery as proposed by didnthaveaname, would be my recommendation. This tweak is another way to deal with the nulls:
/* zero if no details found */
UPDATE POHEADER
SET TOTAL = isnull(PD.TOTAL, 0) -- changed here, assumes you want zero if no details
FROM POHEADER AS PH
LEFT JOIN -- changed here
          (
          SELECT
               PONUMBER
             , sum(quantity * unitCost) AS TOTAL
          FROM PODETAIL
          GROUP BY PONUMBER
          ) AS PD ON PH.PONUMBER = PD.PONUMBER; 

Open in new window

{+ sqlfiddle now up (it was down), added a non-matching poheader (9999) see http://sqlfiddle.com/#!3/dbb12/1 }
0
 
rwheeler23Author Commented:
Thanks for the tips
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.