Updating a table with contents of a cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Updating a table with contents of a cursor

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-27 : 11:57:10

writes "I have a very large table with opening and closing balances. The opening balance is the closing balance of the row immediately before it (of the same account) and I want to reset all of these. Rather than updating the table by cross joining to itself and taking the maximum transaction date before the current transaction date (for a specific acount), I decided it should be quicker to use a cursor. (The MAX(dtTransactionDate) aggregate would otherwise have to be processed across all the rows for each row, whereas a cursor can simply step through the rows remembering the closing balance for the previous row)

The trouble is that the procedure I have now updates the base table one row at a time which is costly. Is there a way to make the updates on the cursor and then have the cursor update the base table in one hit at the end of the procedure?

(I cannot change the database design etc.)

Kind Regards
Julian Haynes
IIS Maidenhead"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-27 : 12:31:57

Well, that's the problem with cursors, they only work one row at a time. The only way you could possibly keep the cursor you have now, but update the base table as a set, is to put the cursor results into a temp table and then JOIN the base table to it. This is a pretty bad way to go about it.

How exactly does your table store this data? Have you tried performing the cross-join UPDATE, and how does it perform if you have? I don't see why you'd use a cursor to traverse the table to update the rows, without simply using UPDATE...WHERE CURRENT OF CURSOR.