CODEWITHSUNDEEP
mysql
Johnson Eddy
Johnson Eddy

Reputation: 41

Looping in MySQL Routine

This below query returns multiple table names.

SET @var2 = 'col1';

SELECT table_name
FROM information_schema.columns
WHERE table_schema = 'db1'
and column_name like @var2

I need to store the values of table_name in array and loop it through an UPDATE statement. So i tried like this. [Rewriting the above query]

SELECT table_name into @var3
FROM information_schema.columns
WHERE table_schema = 'db1'
and column_name like @var2


UPDATE @var3 SET @var2='Test' WHERE 1=1

Actually the UPDATE statement has to run the number of times the count of tables.

I get error since var3 can't hold many values. So i want that to be an array and thus loop it through an UPDATE statement. Any ideas to proceed ?

Upvotes: 0

Views: 71

Answers (1)

Thanu
Thanu

Reputation: 2501

I think you need to loop through cursor within your stored proc. Something like this,

DECLARE t_name VARCHAR(100);
DECLARE not_found_t_names INT DEFAULT 0;

DECLARE cur_table_names CURSOR FOR 
SELECT table_name
FROM information_schema.columns
WHERE table_schema = 'db1'
and column_name like @var2

DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found_t_names = 1;

OPEN cur_table_names;
  SET not_found_t_names = 0;
  t_name_loop : LOOP 
  FETCH cur_table_names INTO t_name;
  IF not_found_t_names THEN
      CLOSE cur_table_names;
      LEAVE t_name_loop;
  END IF;

  SET @update_stmt = CONCAT('UPDATE ',t_name,' SET ',@var2,'=Test WHERE 1=1');

  PREPARE stmt FROM @update_stmt;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

 END LOOP;

Since you are working with a structural element in a variable, its bit tricky, so you need to do the concat correctly. I do not have a environment to test this so please make the syntax corrections your self if there is any to be fixed, but this will be the way to do it I reckon. Should work, good luck!

Upvotes: 1

Related Questions