?
Solved

Update cursor updating the same record

Posted on 2011-10-14
7
Medium Priority
?
302 Views
Last Modified: 2012-05-12
I wrote a cursor.  It outputs the correct priceid list but still updates just one priceid into my new temp table feld:

 DECLARE @priceid varchar(50)
DECLARE getpriceid CURSOR
   SET @getpriceid = CURSOR FOR Select tmpric.prive_ID  FROM UPLOADed_prices as LC
              LEFT JOIN
              UPLOADed_prices tmpric
              ON Lc.PARENT_PRICE_ID=TMPRIC.INTERNAL_PRICE_ID
             WHERE Lc.PRICE_NUM = '777777'
             AND Lc.PRICE_ID IS not NULL              
             
         
             
OPEN getPRICEid
FETCH NEXT
FROM getPRICEID INTO @PRICEid
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @PRICEid  
 FETCH NEXT
FROM getPRICEid INTO @PRICEid
UPDATE #tmpPRICETABLE set NEW_MASTER_ID  = @PRICEID
where #tmpPRICETABLE.NEW_PRICE_NUM = 777777
AND  #tmpPRICETABLE.NEW_PRICE_ID IS not NULL  
END
CLOSE getPRICEid
DEALLOCATE getPRICEid
This is the last step to this process...any suggestions
0
Comment
Question by:programmher
  • 4
  • 3
7 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36967787
Your fetch statement should be the last thing in the while block. You have already fetched the first line outside of the while block.

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @PRICEid  
UPDATE #tmpPRICETABLE set NEW_MASTER_ID  = @PRICEID
where #tmpPRICETABLE.NEW_PRICE_NUM = 777777
AND  #tmpPRICETABLE.NEW_PRICE_ID IS not NULL  
 FETCH NEXT
FROM getPRICEid INTO @PRICEid

END
0
 

Author Comment

by:programmher
ID: 36968006
lsavidge:,

I made the above suggested modification and I still get the same record updated into my column.  My output dispays the two records that should be updated - I just can't get the update statement to actually update the correct corresponding record into my temp table column...what am I missing?!?
0
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 36968033
Maybe your update statement is wrong. Change it to this temporarily:

select * from #tmpPRICETABLE
where #tmpPRICETABLE.NEW_PRICE_NUM = 777777
AND  #tmpPRICETABLE.NEW_PRICE_ID IS not NULL

And see how many records are returned.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:programmher
ID: 36968459
The select statement returned 10 rrecords - 8 have one price code and 2 have a different price code.  That is the correct result; but, the udpate statement with the same criteria/parameters update all ten records with the same price code  - not 8 records with one price code and 2 records with the other price code.   It's almost as if the cursor isn't looping or something...
0
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 200 total points
ID: 36968764
Ok so each time your cursor loops it will upate all those records in that select statement which means your update statement is wrong. Your update statement in the loop should (technically) be updating a single record.
0
 

Author Comment

by:programmher
ID: 36969126
What is the correct update statement so it will loop through and update the correct 8 corresponding records then the correct 2 corresponding records with the correct price code?
0
 

Author Closing Comment

by:programmher
ID: 36970464
Thanks for the input - I added an additiona; condition to my update statement and the results are now accurate!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question