GROUP BY in Update Statement 1

[ben1234zz]

Hi

I am struggleing to create this update statement with a select/group by clause:

UPDATE PP SET PP.Ex = SUM(LPP.Ex)
FROM SM_Prod_Pricing PP
INNER JOIN SM_NFW_Bundle_Pricing_Link BP
ON PP.ID = BP.Pricing_ID
INNER JOIN SM_Prod_Pricing LPP
ON BP.Linked_Pricing_ID = LPP.ID
GROUP BY BP.Pricing_ID

It works fine with the equivelent Select statement but the group by causes an error in the update.

Any assistance would be much appreciated.

Thanks
B

 

[fredericofonseca]

The following may not be absolutely correct, but at least will point youon the right direction.

UPDATE PP SET PP.Ex = sum_Ex
FROM SM_Prod_Pricing PP
INNER JOIN (select pricing_id, sum(lpp.ex) as sum_ex
from SM_NFW_Bundle_Pricing_Link BP
INNER JOIN SM_Prod_Pricing LPP
ON BP.Linked_Pricing_ID = LPP.ID
GROUP BY BP.Pricing_ID ) lpp
ON pP.id = LPP.Pricing_ID

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ben1234zz]

Thanks that's ideal!!

B