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
How is it failing? Are you getting a specific error or is it providing results other than you expect?
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.
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
