-- 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
record #1 IKEY = 101 | MyField = 10 | rTYPE = Item >record #2 IKEY = 102 | MyField = 20 | rTYPE = Item >record #3 IKEY = 103 | MyField = 30 | rTYPE = Item >record #4 IKEY = 100 | MyField = 00 | rTYPE = Rollup >record #5 IKEY = 201 | MyField = 18 | rTYPE = Item >record #6 IKEY = 202 | MyField = 24 | rTYPE = Item >>Where the total that should be written to MyField for record IKEY 100 should be 60. I have pulled out all but the meat of the query here:
>-- Roll up the values >select > sum(MyField) as RollUp >from foo > where left(IKey,1) = '1' > > -- insert the rolled up values > update foo > set > MyField = RollUp >where IKey = '100' >>