Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Cursors |
Sat, Jan 9 2010 11:51 AM | Permanent Link |
Paul | Hello, I have a problem with Cursors.
My Proc looks like this: CREATE PROCEDURE UpdateState() BEGIN DECLARE CustCursor CURSOR WITH RETURN FOR Stmt; DECLARE State CHAR(2) DEFAULT ''; PREPARE Stmt FROM 'SELECT * FROM Customer WHERE State=''FL'''; OPEN CustCursor; FETCH FIRST FROM CustCursor (State) INTO State; WHILE NOT EOF(CustCursor) DO UPDATE CustCursor SET State='NY'; FETCH NEXT FROM CustCursor (State) INTO State; END WHILE; END Only the half of selected records are updated. I think because the field "State" is in the clause WHERE. I don't want to use somethink like "if... end if". How can I walk around ? Thank's in advance. Paul. |
Sat, Jan 9 2010 1:58 PM | Permanent Link |
"Terry Swiers" | Paul,
> Only the half of selected records are updated. I think because the field > "State" is in the clause WHERE. Updating the current record to change the state value will make it no longer appear in the results list which then places the cursor on the next record. When you call the FETCH NEXT, it moves it forward again. So you are basically updating every other record. Two suggestions. The first is to replace the FETCH NEXT with a FETCH FIRST which should always put you on the first record. Secondly, if you are updating all of the customers that are in FL to NY, why not just run a simple update statement like: update cust set state = 'NY where state = 'FL' instead of having to deal with walking the cursor. -- --------------------------------------- Terry Swiers Millennium Software, Inc. http://www.1000years.com http://www.atrex.com Atrex Inventory Control/POS - Big business features without spending big business bucks! Atrex Electronic Support Options: Atrex Knowledgebase: http://www.atrex.com/atrexkb.asp Email: mailto:support@atrex.com Newsgroup: news://news.1000years.com/millennium.atrex Fax: 1-925-829-1851 Phone: 1-925-828-5892 (M-F, 9a-5p Pacific) --------------------------------------- |
Sun, Jan 10 2010 5:23 AM | Permanent Link |
Uli Becker | Paul,
as Terry indicated the reason for your problem is that you declared a sensitive cursor. Generally you can use an insensitive cursor to iterate the resulting dataset without changing it. Of course then you cannot update the cursor. Instead you have you use an update statement like Execute Immediate 'update MyTable set State = ? where ID = ? and state = ''NY''' using MyState,MyId ; Anyway solution #2 by Terry should do the trick. Regards Uli |
Sun, Jan 10 2010 8:52 AM | Permanent Link |
Paul | Thank you Terry, Thank you Uli for your Help.
I don't want to update all records. My real proc is a big proc and i give you just a sample. Finally i found the error. Just use "FETCH RELATIVE 0" instead of "FETCH NEXT" and this when updating field used in the clause "WHERE". It's same when deleting Records inside Cursors. Regards Paul |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |