Stored Procedure Data Types

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_updateStr ing)-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=gcdmg-mysql-2@m.gmane.org

Re: Stored Procedure Data Types

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_updateStr ing)-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=gcdmg-mysql-2@m.gmane.org