I would like to update a record in

[sagn]

I would like to update a record in a table
with columns c and d, where d is the max(sum(data))
for a.

But I get an error saying you cannot have an aggregate
of an aggregate. So I devised the following involving
two extra steps. The ultimare goal is step 3,
to update table 2 (our new table created from table1) with the
max of the sum of d for a,b. There must be a more elegant solution. What is it? Thanks.

ds

/* Step 1 */
select t1.a,t1.b,t2.c,sum(t2.d) as sumd
into #temp
from table1 t1, table2 t2 -- t1 is the original table
-- t2 is the new table

where
t1.a=t2.a
and
t1.b=t2.b
group by t1.a,t1.b,t2.c

/* Step 2 */

select t1.a,t1.b,t1.c,max(t1.sumd) as maxsumd
into #temp2
from #temp t1, table2 t2
where
t1.a=t2.a
and
t1.b=t2.b
group by t1.a,t1.b,t2.c

drop table #temp1

/* Step 3 */
update t2
set
c = t1.c,
d = t1.maxsumd
from #temp2 t1,table2 t2
where
t1.a=t2.a
and
t1.b=t2.b

drop table #temp2

 

[tlbroadbent]

I'm confused by your query. You summarize table2.d and then update table2.d with that value. You also update table2.c with the value in table2.c. There must be something I've missed or that you've not told us.

Regardless, the following query avoids the use of temporary tables while accompplishing the same function. I'm not sure it would be called "more elegant". In fact, it may be harder to understand and therefore maintain.
[tt]
Update table2 Set d=s2.maxsumd

From table2 t3 Inner Join
(Select a, b, c, max(sumd) As maxsumd

From
(Select t1.a, t1.b, t2.c, sum(t2.d) as sumd

From table1 t1 Inner Join table2 t2
On t1.a=t2.a and t1.b=t2.b

Group By t1.a, t1.b, t2.c) As s1

Group By a, b, c) As s2
On t3.a=s2.a And t3.b=s2.b[/tt] Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[sagn]

Ahhhh I see using 2 'FROM's in the update...

Table1 is the table from which
I got data to put into table two but one of the
fields in table2 is the max of the sum of d
and depends on the value of a flag in table
one. (in table one there will be several values
of a and b and a flag value x)

I update table2 with a value from the temp table.
I don't update table2 from anything that was in
table 2...

I am sorry if that was not claer. I think my notation
was the best as sometimes table2 was equated with t2 and sometimes with t1.