question

sqlnewb avatar image
sqlnewb asked

First Row Changing to Null

I have a table that has 3 columns: I am adding diagonally Current Table ID BASE STACK 1 2 O 2 Null 3 3 NUll 4 4 Null 2 Result I want: ID BASE STACK 1 2 O 2 5 3 3 9 4 4 11 2 I am using this update statement which works correctly EXCEPT everytime I run it it Nulls out my first row of Base 2 and sets it to zero. I need that 2 to be part of the calculation update a8 set base = (isnull(T2.CumulativeVar,0) + a8.Stack) from ##temp_test5 a8 cross apply (select sum(Stack) CumulativeVar from ##temp_test5 where id < a8.id and a8.id > 1) T2
sql-server-2008tsqlupdate
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image
Scot Hauder answered
UPDATE a8 SET base = ISNULL(T2.CumulativeVar,0) + a8.Stack FROM ##temp_test5 a8 CROSS APPLY (SELECT SUM(Stack) + (SELECT base FROM ##temp_test5 WHERE id = 1) CumulativeVar FROM ##temp_test5 WHERE id < a8.id) T2 WHERE a8.id > 1 -- or -- UPDATE a8 SET base = (SELECT SUM(Stack) FROM ##temp_test5 WHERE id <= a8.id) + (SELECT base FROM ##temp_test5 WHERE id = 1) FROM ##temp_test5 a8
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlnewb avatar image sqlnewb commented ·
Thank You that second query produces the correct results...I have been scratching my head over this problem. Much appreciated
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
Put `isnull` around the nullable part of the calculation i.e. set base = (isnull(T2.CumulativeVar,0) + a8.Stack) and add a filter to the update where a8.id > 1
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlnewb avatar image sqlnewb commented ·
@Kev Riley...I tried that but it still changes my base for id 1 to ZERO and then begins the calculations using that zero
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
sorry, quick reply and didn't read the question properly...edited above!
0 Likes 0 ·
sqlnewb avatar image sqlnewb commented ·
@kev Riley..I also tried that before posting to here. That still changes the id 1 of the base column to ZERO...This is very odd...I edited my original question to show the changes I have made to the update. Any other ideas what could be wrong?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.