SQL Server has an extension to the UPDATE statement that provides a FROM clause. I'm wondering if something like this would work:

UPDATE foo SET
myField1 = SUM(myField1)
,myField2 = SUM(myField2)
FROM
foo f1
INNER JOIN foo f2 ON f2.ikey LIKE LEFT(f1.iKey, 1)+'%' AND f2.iKey != LEFT(f1.iKey, 1)+'00'
GROUP BY
LEFT(f1.iKey, 1)

But, I wonder if you wouldn't be better off trying to maintain the total in real time with a couple of triggers?

Also, it seem to me that it would be easier if the totals were maintained in another table instead of trying to allocate a row in the detail table to it. At a minimum, the code would be much faster if you had a flag that you could key off of instead of looking for an iKey value with a specific format.

Just my $0.02

-Mike

>In the code I actually use the alias 'REQQTR1' etc but I didn't know rollup was a keyword. Michael, I am going to try and impliment your shorter solution tomorrow. Is there a way to get the job done with a single SELECT for my 13 or so sum fields? Or is the method here the way. I anticipate this table will have between 300,000 to 600,000 records on upper end equipment. So is the multiple SELECT a bad hit?
>
>

>UPDATE foo
>SET myField = (SELECT SUM(myField) FROM foo WHERE ikey LIKE '1%' AND ikey != '100')
>SET myField1 = (SELECT SUM(myField1) FROM foo WHERE ikey LIKE '1%' AND ikey != '100')
>SET myField2 = (SELECT SUM(myField2) FROM foo WHERE ikey LIKE '1%' AND ikey != '100')
>-- ETC
>WHERE iKey='100'
>

>
>My thanks again for your help,
>Joe
>
>>It doesn't work because Rollup is an alias for a column and the results were returned to the client. To get the behavior using your example, you'd probably do something like this:
>>
>>

>>-- Roll up the values
>>DECLARE @rollup int
>>select
>>    @rollup = sum(MyField)
>>from foo
>>    where left(IKey,1) = '1'
>>
>> -- insert the rolled up values
>> update foo
>>     set
>>        MyField = @RollUp
>>where IKey = '100'
>>

>>
>>Now, a better solution would be to stay in the set based operations. So, you could also write:
>>
>>

>>UPDATE foo
>>SET myField = (SELECT SUM(myField) FROM foo WHERE ikey LIKE '1%' AND ikey != '100')
>>WHERE iKey='100'
>>

>>
>>-Mike
>>
>>>Why does this not Work? I am certain I have oversimplified the mighty T-SQL language. I have some records that get a rollup value based
>[SNIP]