Are you sure that you can't write this in a set-oriented fashion using an update with a subquery:
UPDATE orders SET
total = (SELECT SUM(cnt * cost) FROM orderdetails WHERE orderid = orders.orderid)
-Mike
>I've been trying to write an SP that has two cursors, the first (outer) I need to be able to do updates with, the second (inner) is read-only. I've DECLAREd the first cursor as follows:
>
>DECLARE corrCursor CURSOR FOR
> SELECT * from v_Open_Correspondence
> FOR UPDATE Item_Age
>
>Then I open the cursor and use FETCH NEXT to loop through the items in the View. For each item I open a read-only cursor for a subsidiary table, loop through the related items in the subsidiary table to compute the Item_Age value of the parent item. Once I'm done with the subsidiary table I close that cursor and try to do
>
>UPDATE v_Open_Correspondence
> SET Item_Age = @Age
> WHERE CURRENT OF corrCursor
>
>to update that column in the current row for corrCursor.
>
>My problem, despite passing the syntax check in Enterprise Manager Stored Procedure Properties, I receive an error trying to execute the SP in Query Analyzer that says:
>
>Server: Msg 16957, Level 16, State 4, Procedure AgeItems, Line 9
>FOR UPDATE cannot be specified on a READ ONLY cursor.
>
>Line 9 is the DECLARE for corrCursor.
>
>Is there something about cursors in SP's that doesn't match the T-SQL definition of how cursors should work? This code was copied almost verbatim from several authoritative sources on T-SQL, but it doesn't work.
>
>Any suggestions?
>TIA