How to loop through all the tables on a database to update columns

Question

I'm trying to update a column (in this case, a date) that is present on most of the tables on my database. Sadly, my database has more than 100 tables already created and full of information. Is there any way to loop through them and just use:

UPDATE SET date = '2016-04-20' WHERE name = 'Example'

on the loop?

A database with 100 tables can sometimes be symptomatic of poor design — Strawberry, May 21 '16 at 12:17

Tim Biegeleisen · Answer 1 · 2016-05-21T15:11:34.193

13

One painless option would be to create a query which generates the UPDATE statements you want to run on all the tables:

SELECT CONCAT('UPDATE ', a.table_name, ' SET date = "2016-04-20" WHERE name = "Example";')
FROM information_schema.tables a
WHERE a.table_schema = 'YourDBNameHere'

You can copy the output from this query, paste it in the query editor, and run it.

Update:

As @PaulSpiegel pointed out, the above solution might be inconvenient if one be using an editor such as HeidiSQL, because it would require manually copying each record in the result set. Employing a trick using GROUP_CONCAT() would give a single string containing every desired UPDATE query in it:

SELECT GROUP_CONCAT(t.query SEPARATOR '; ')
FROM
(
    SELECT CONCAT('UPDATE ', a.table_name,
                  ' SET date = "2016-04-20" WHERE name = "Example";') AS query,
        '1' AS id
    FROM information_schema.tables a
    WHERE a.table_schema = 'YourDBNameHere'
) t
GROUP BY t.id

edited May 21 '16 at 15:11

answered May 19 '16 at 04:39

Tim Biegeleisen

502,043
27
286
360

would this affect just the tables on the database I selected or all the tables on all the databases I have on my server? – Myuu May 19 '16 at 04:55
Just add a `WHERE` clause to refer to your database. – Tim Biegeleisen May 19 '16 at 05:00
I would also add a condtition to check if the table has the columns `date` and `name`. something like: `where .. and exists(select 1 from information_schema.columns c where c.table_name = a.table_name and c.column_name = 'date') and exists(select 1 /* same for column 'name' */)` – Paul Spiegel May 21 '16 at 14:05
I would also use `group_concat(... separator ';')`- so one can just run -> copy -> paste -> run. – Paul Spiegel May 21 '16 at 14:16
@PaulSpiegel I wouldn't use `GROUP_CONCAT()` here because we are not aggregating anything here. But thanks for the heads up, I added a semicolon to the end of each generated `UPDATE` statement. – Tim Biegeleisen May 21 '16 at 14:19
So you would also never use `count(*)` without `group by`? – Paul Spiegel May 21 '16 at 14:26
I don't know whether workbench allows for copying an entire column out. If it doesn't then your idea is interesting. – Tim Biegeleisen May 21 '16 at 14:34
I'm using HeidiSQL and would need to export a multiple row result as delimited text with '' as separator. That are some more actions to do. But i wouldn't care much for a one time task. However i still think a check for column existence is important. – Paul Spiegel May 21 '16 at 14:52
@PaulSpiegel I use HeidiSQL and agree that it could be a hassle. I updated my answer to use a subquery with `GROUP_CONCAT`, because I don't know if it would work without `GROUP BY` in all versions of MySQL. – Tim Biegeleisen May 21 '16 at 15:15

Andrew · Answer 2 · 2016-05-21T12:11:54.603

You can use SHOW TABLES command to list all tables in database. Next you can check if column presented in table with SHOW COLUMNS command. It can be used this way:

SHOW COLUMNS FROM `table_name` LIKE `column_name`

If this query returns result, then column exists and you can perform UPDATE query on it.

Update

You can check this procedure on sqlfiddle.

CREATE PROCEDURE UpdateTables (IN WhereColumn VARCHAR(10),
                               IN WhereValue VARCHAR(10),
                               IN UpdateColumn VARCHAR(10),
                               IN UpdateValue VARCHAR(10))
BEGIN
  DECLARE Finished BOOL DEFAULT FALSE;
  DECLARE TableName VARCHAR(10);

  DECLARE TablesCursor CURSOR FOR
    SELECT c1.TABLE_NAME
    FROM INFORMATION_SCHEMA.COLUMNS c1
      JOIN INFORMATION_SCHEMA.COLUMNS c2 ON (c1.TABLE_SCHEMA = c2.TABLE_SCHEMA AND c1.TABLE_NAME = c2.TABLE_NAME)
    WHERE c1.TABLE_SCHEMA = DATABASE()
      AND c1.COLUMN_NAME = WhereColumn
      AND c2.COLUMN_NAME = UpdateColumn;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET Finished = TRUE;

  OPEN TablesCursor;

  MainLoop: LOOP
    FETCH TablesCursor INTO TableName;
    IF Finished THEN
      LEAVE MainLoop;
    END IF;

    SET @queryText = CONCAT('UPDATE ', TableName, ' SET ', UpdateColumn, '=', QUOTE(UpdateValue), ' WHERE ', WhereColumn, '=', QUOTE(WhereValue));
    PREPARE updateQuery FROM @queryText;
    EXECUTE updateQuery;
    DEALLOCATE PREPARE updateQuery;
  END LOOP;

  CLOSE TablesCursor;
END

This is just an example how to iterate through all tables in database and perform some action with them. Procedure can be changed according to your needs.

I wanted to do that, use show tables and then maybe using a while? to do the updating, I'm just lost on how to do it — Myuu, May 19 '16 at 04:44
You want to do all work inside MySQL or with some outside script? — Andrew, May 19 '16 at 04:46
Ideally just on phpmyadmin. I just want to update all those tables once, or being able to update them easily like that — Myuu, May 19 '16 at 04:47
@Myuu, I have added procedure example for update many tables. — Andrew, May 21 '16 at 12:13

score 0 · Answer 3 · edited May 23 '17 at 12:24

0

Assuming you are using MySQL, You can use Stored Procedure.

This post is a very helpful.

Mysql-loop-through-tables

edited May 23 '17 at 12:24

Community

1
1

answered May 19 '16 at 04:39

Minjun Yu

3,497
4
24
39

How to loop through all the tables on a database to update columns

3 Answers3

Update

Linked