How can I loop through all rows of a table? (MySQL 5.5.46 version hosting)

Databases
1

Hi,

I need something like ‘for each record null in table A’, update table A where the condition, using MySql 5.5.46.

My first approach was create a SP with this lines

CREATE DEFINER=`tUser`@`%` PROCEDURE `ROW_PER_ROW`()
BEGIN

DECLARE n INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE tYear INT(4);

SET tYear = YEAR(CURDATE());

SET GLOBAL innodb_lock_wait_timeout = 50000;       
SET innodb_lock_wait_timeout = 50000;

SET @s = CONCAT('SELECT COUNT(*) FROM `tbl_A_',tYear,'_new`
                 WHERE tType IS NULL INTO @n;');                                                            
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
    
SELECT @n;

SET i=0;

WHILE i<@n DO 

SELECT i;

SET @s = CONCAT('UPDATE `tbl_A_',tYear,'_new`
                 SET tType = ''IB''
                 WHERE
                 tDecription IN (''SGA'')
                 AND tType IS NULL
                 LIMIT i, 1;');
                                 
SELECT @s;
                     
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET i = i + 1;
SELECT i;

END WHILE;

END

The value of @n it’s 169150 (correctly).

The error is

Procedure execution failed 1327 - Undeclared variable: i Time: 0,459s

Help me to do it…

2

why are you looping? you can do it all in one statement

UPDATE tbl_A_2021_new
   SET tType = 'IB'
 WHERE tDecription = 'SGA'
   AND tType IS NULL
1 Like
3

Especially given that the first count may be higher than the number of valid targets for the second query, resulting in wasted loop attempts. (The initial count doesn’t include the Decription conditional)

1 Like
4

Thanks for reply, I think I use loop because update a 6 millions rows table … I would like to do it in sections

5

why? do you have any idea how long the single query will actually take?

if you’re still worried, you can “section” on a range of primary key values, as narrow a range as you wish

but really, there is no need to loop – that’s ~so~ inefficient

UPDATE ...
   SET ...
 WHERE id BETWEEN 1 AND 100000
   AND ...
   AND ...
UPDATE ...
   SET ...
 WHERE id BETWEEN 100001 AND 200000
   AND ...
   AND ...
1 Like
6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.