MySql loop through several tables with similar names

Question

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!

FYI, The question you referred to is for SQL-Server, not MySQL. — Barmar, Oct 30 '19 at 05:09
Query `INFORMATION_SCHEMA.TABLES` to find all the tables that match the naming pattern. Loop over the result and generate dynamic SQL with a `PREPARE` statement. — Barmar, Oct 30 '19 at 05:13
See https://stackoverflow.com/questions/7523039/mysql-query-to-search-all-tables-within-a-database-for-a-string for an example of how to do a similar loop to create `SELECT` queries. — Barmar, Oct 30 '19 at 05:13
Thank you @Barmar. I can't quite get it to run though. Maybe I should just use PHP in order to get it done? — wbq, Oct 30 '19 at 16:21
That would almost certainly be easier. But if you want to learn how to do it, post what you tried and we'll help you fix it. — Barmar, Oct 30 '19 at 16:23

score 2 · Accepted Answer · answered Nov 02 '19 at 11:55

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!

MySql loop through several tables with similar names

1 Answers1