CODEWITHSUNDEEP
mysqlloopsiterator
wbq
wbq

Reputation: 662

MySql loop through several tables with similar names

I want to loop through several mysql tables that have similar names like

table_1_user

table_2_user

table_3_user

In sum I want an iterator count up to 20 so that in the end I will have fired some queries over all 20 tables. As you can see the only difference is the number within the name. So I made some research and found this answer by Martynnw. One drawback I have to mention is that there are one or two ids missing so it can be that we have a table_3 and a table_5 but the table_4 might be missing. So I would also like to include a check if the table exists (if necessary at all).

I believe it has to be possible to do something like this:

Declare @Id int

While @Id < 20
Begin

    UPDATE table_@Id_user SET option_value = 'mychange' WHERE my_id = 123;

End

But I can't quite figure it out yet.

Any help would be very much appreciated!

Thanks a lot in advance!

Upvotes: 0

Views: 610

Answers (1)

wbq
wbq

Reputation: 662

So.. a couple of days later I figured it out myself.

Fortunately there was another table holding all the ids I needed in order to make the updates I was looking for.

So in addition to 

table_1_user
table_2_user
table_3_user

There was a table holding the ids:

TABLE users

user_id | user_name | ...
1         max
2         justin
...       ...

So in a first step I had to create a temporary table:

CREATE TEMPORARY TABLE temp
SELECT user_id FROM users;

Next I had to create a procedure in order to be able to use while loops:

DELIMITER $$

DROP PROCEDURE IF EXISTS Update_User_URLS;
CREATE PROCEDURE Update_User_URLS()

BEGIN

...

END $$
DELIMITER ;

CALL Update_User_URLS();

Then I had to add the needed while loop selecting a user_id from the temporary table. At the end of the loop this user_id had to be removed from the temp table: 

DELIMITER $$

DROP PROCEDURE IF EXISTS Update_User_URLS;
CREATE PROCEDURE Update_User_URLS()

BEGIN

    WHILE (SELECT COUNT(*) FROM temp) > 0 DO

        SELECT @id := user_id FROM temp LIMIT 1;

            ...

        DELETE FROM temp WHERE user_id = @id;

    END WHILE;

END $$
DELIMITER ;

CALL Update_User_URLS();

Within the while loop I had to set a variable as concatenated table name and use PREPARE for my designated query as well as EXECUTE in order to fire it. 

DELIMITER $$

DROP PROCEDURE IF EXISTS Update_User_URLS;
CREATE PROCEDURE Update_User_URLS()

BEGIN

    WHILE (SELECT COUNT(*) FROM temp) > 0 DO

        SELECT @id := user_id FROM temp LIMIT 1;

            SET @table = concat('table_', @id, '_user');
            SET @query = concat('UPDATE ', @table, 'SET user_url = replace(user_url, '"example.com"', '"anotherexample.com"')')

            PREPARE statement from @query;
            EXECUTE statement;

        DELETE FROM temp WHERE user_id = @id;

    END WHILE;

END $$
DELIMITER ;

CALL Update_User_URLS();

And it worked for me!

Upvotes: 2

Related Questions