question

sqlnewb avatar image
sqlnewb asked

Update with subquery

Does anyone know why this update statement is not working for me? I've been playin around with it. The Query produces the correct results when I run it without the update. I want to update the base column Example of the results I am looking for. Add diagonally id base stack 1 2 Null ` `2 8 6 3 10 2 4 15 5 etc... update a3 set base = (select (T2.CumulativeVar + T1.Stack) from ##temp_test5 T1 cross apply (select sum(Stack) CumulativeVar from ##temp_test5 where id < T1.id) T2) from ##temp_test5 a3
sql-server-2008t-sqlupdate
2 comments
10 |1200 characters needed characters left characters exceeded

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

How is it failing? Are you getting a specific error or is it providing results other than you expect?
0 Likes 0 ·
One way it would definitely fail is if your subquery returns more than one result per row in a3, and since the subquery does not look correlated with a3 at first glance, that seems somewhat likely.
0 Likes 0 ·

1 Answer

· Write an Answer
Håkan Winther avatar image
Håkan Winther answered
The set statement can only handle one record at a time and your subqueries probably returns more than one record. You will have to create a from clause and place yor subqueries ther an make sure the from clause is joined to the table to update. Another thing to remember is that the update statement doesnt warn you if the From clause give you more then one record for each record to update. That may give you unpredictable results. Take a look at merge instead
1 comment
10 |1200 characters needed characters left characters exceeded

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

Yes that is the error I am getting. Subquery returned more than 1 value.
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.