While updating records using cursor

All telephone numbers in the Phone column in the Customers table are stored as char(12) strings of the following format, ‘999-999-9999’. = 500 OPEN Phone Cursor FETCH NEXT FROM Phone Cursor WHILE @@FETCH_STATUS = 0 BEGIN UPDATE Customers SET Phone = ‘222’ SUBSTRING(Phone,5,8) WHERE CURRENT OF Phone Cursor FETCH NEXT FROM Phone Cursor END CLOSE Phone Cursor DEALLOCATE Phone Cursor The big update will hold a transaction against the database for, potentially, a long time... For this, I would recommend a cursor to spread that load out over a period of time. something like this: The cursor would be far slower in any large dataset, talk about hours vice seconds or milliseconds.

i must make the appropriate changes to the Customers table as quickly as possible using the least administrative effort. What it will do is not lock up the database from other users for as long at any one time.

Then I would try the set-based approach which runs a batch of the records only if the set-based is too slow.

If you have to go the cursor running one record at time, then there is probably something massively wrong with your database design.

A company uses a SQL Server database to store information about its customers and its business transactions. The area code 111 remains the same for telephone numbers with prefixes that are less than 500.

The numbers with prefixes that are 500 and greater will be assigned an area code of 222.

On this occasion I want to update a field in the temporary table, yet I can't figure out how to do it.

In the example below, I'm trying to update the field Current POs in temporary table #t1, based upon a query that uses to reference this, but it doesn't work. Address Type = '1801') - (select sum() from Purchase Order Line POL inner join Supplier Address SA ON POL. Supplier_ID inner join Purchase Order Header POH ON POH.

= Name WHEN NOT MATCHED THEN INSERT (ID, Name) VALUES (Id, Name) To actually answer your question I would do it something like as follows.But mere pointing isn't gonna make sense unless it can be used. This takes data from the recordset, stores it in the variable(s) provided.so if you remove the first fetch statement the while loop won't work as there is not "FETCHED" record for manipulation, if you remove the last fetch statement, the "while" will not loop-through.My difficulty is that I'm having trouble finding an example of how to update a column fetched by a cursor.Previously when I've used cursors I've retrieved values into variables, then run an update query at each step based upon these values.

Search for while updating records using cursor:

while updating records using cursor-52

The better question how to get rid of the cursor and try to solve your query without it.

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “while updating records using cursor”