Hi, Use a temporary table to store the ids and join to it for the final update? That will at least avoid an error when the cursor selects zero records.
Cheers, -Janek On Wed, 2009-05-20 at 16:05 -0400, W. Scott Hayes wrote: > Hello, > > I would like to do a select on a table to get back the IDs of some of > the records. > > Then take those IDs and do a single update using a WHERE clause like > (recordID IN (2,44,21)) > > My question is: > > Can I build a string using a cursor that has all of the IDs and then > issue an update using the string as part of the WHERE clause? > > Are there functions that facilitate this better? I'm wondering if > there is some sort of column function that will grab the IDs from the > initial select. > > Below is my code. Thanks for any advice. > > DELIMITER $$ > > DROP PROCEDURE IF EXISTS sp_getNextQueueBlock$$ > > CREATE PROCEDURE sp_getNextQueueBlock() > BEGIN > DECLARE l_LinkQueueID INTEGER; > DECLARE no_more_queue_items INT DEFAULT 0; > DECLARE l_updateString VARCHAR(2000) DEFAULT ''; > DECLARE queue_csr CURSOR FOR > SELECT LinkQueueID FROM linkqueue WHERE Completed <> 0 LIMIT 200; > DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_queue_items=1; > > START Transaction; > OPEN queue_csr; > queue_loop:LOOP > FETCH queue_csr INTO l_LinkQueueID; > IF no_more_queue_items=1 THEN > LEAVE queue_loop; > END IF; > SET l_updateString=CONCAT(l_updateString,', ',l_LinkQueueID); > END LOOP queue_loop; > > IF LENGTH(l_updateString) > 2 THEN > SET l_updateString=SUBSTRING(l_updateString,3,LENGTH(l_updateString)-2); > END IF; > > UPDATE linkqueue SET Completed = 0 WHERE (LinkQueueID IN (l_updateString)); > commit; > END$$ > > DELIMITER ; > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
