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
