November 19, 2002 at 2:31 pm
"An aggregate may not appear in the set list of an UPDATE statement"
I've gotten this message enough from SQL to know it's not possible, but what I don't know is how to duplicate the functionality of the offending statement. Any help?
Here is a sample of what I am trying to do, but fails. The goal is to update a table of States (stateSummary) with the data from another table (leadDistribution) in one fell swoop. The states table already has State data in it so I can’t use an insert.
update #stateSummary
set
leadsTotal = count(stateOrProvince)
, revenueGross = sum(leadRevenue)
, leadDistributed = sum(leadCount)
, leadCost =sum(leadRate)
from
#leadDistributionSummary
where
#stateSummary.stateOrProvince = #leadDistribution.stateOrProvince
group by
stateOrProvince
November 19, 2002 at 2:44 pm
Use a derived table
update ss
set
leadsTotal = stateOrProvince
, revenueGross = Revenuegross
, leadDistributed = leaddistributed
, leadCost =leadCost
from (
select leadsTotal = count(stateOrProvince)
, revenueGross = sum(leadRevenue)
, leadDistributed = sum(leadCount)
, leadCost =sum(leadRate)
, stateorprivince
from
#leadDistributionSummary
group by
stateOrProvince
) a
inner join
#statesummary ss
on sS.stateOrProvince = a.stateOrProvince
Steve Jones
November 19, 2002 at 2:46 pm
Try this:
update #stateSummary
set
leadsTotal = count_stateOrProvince
, revenueGross = sum_leadRevenue
, leadDistributed = sum_leadCount
, leadCost =sum_leadRate
from (select stateorprovince, count(stateOrProvince) as count_stateorProvince,
sum(leadRevenue) as sum_leadrevenue,
sum(leadCount) as sum_leadcount,
sum(leadRate) as sum_leadrate from
#leadDistributionSummary
group by
stateOrProvince
) a
where
#stateSummary.stateOrProvince = a.stateOrProvince
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
November 19, 2002 at 2:48 pm
Try this,I have assumed you have an ID column.
update #stateSummary
SET leadsTotal = COUNT(Tem.leadsTotal)
, revenueGross = Tem.revenueGross
, leadDistributed = Tem.leadDistributed
, leadCost = Tem.leadCost
FROM #stateSummary INNER JOIN(
select ID = ID
,leadsTotal = stateOrProvince
, revenueGross = sum(leadRevenue)
, leadDistributed = sum(leadCount)
, leadCost =sum(leadRate)
from
#leadDistributionSummary
where
#stateSummary.stateOrProvince = #leadDistribution.stateOrProvince
group by stateOrProvince,ID) Tem
ON #stateSummary.ID = Tem.ID
November 19, 2002 at 3:06 pm
Thanks for ALL the quick responses. I took the first one I saw and tried it. Bingo! Just in case anybody is interested here is my implementation. I just had to add the aliases and it worked perfect. I had given up on this a few weeks ago and used a couple of update statements. I guess I should have just asked 🙂
update ss
set
leadsTotal = a.leadsTotal
, revenueGross = a.Revenuegross
, leadDistributed = a.leadDistributed
, leadCost = a.leadCost
from
(
select
leadsTotal = count(lds.stateOrProvince)
, revenueGross = sum(lds.leadRevenue)
, leadDistributed = sum(lds.leadCount)
, leadCost =sum(lds.leadRate)
, stateOrProvince
from
#leadDistributionSummary lds
group by
lds.stateOrProvince
) a
inner join
#statesummary ss
on sS.stateOrProvince = a.stateOrProvince
November 20, 2002 at 3:32 am
Just a word, if would probably be quicker to populate this columns at the same time you are populating #statesummary, rather than updating it, this is especially true if you are populating #statesummary in one go or there are a lot of rows in #statesummary
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply