bcg bcg - 2 months ago 9
MySQL Question

Execute same query in several tables

How can execute in mysql a query that executes the sentence in several tables if exist. For example:

I can have the following tables: w_extra_content, w_extra_content_gl, w_extra_content_dn.

Is there any way to run something like:

update name = "example" from w_extra_content * where name = "old_example"


and that the query is executed on all tables that match the name?

In the tables only varies the final completion but you may not know that termination is in the database.

It can only be in mysql.

Answer

Use the below query

SET @@group_concat_max_len = 1000000;
SET @query := (SELECT GROUP_CONCAT(CONCAT_WS(' ','update ',TABLE_NAME,' set name = \'example\' where name = \'old_example\'') 
                    SEPARATOR ';')
              FROM 
              ( SELECT Table_NAME
              FROM information_schema.tables 
              WHERE TABLE_NAME LIKE 'w_extra_content%') tab);    
     PREPARE stmt FROM @query;
     EXECUTE stmt;
     DEALLOCATE PREPARE stmt ;
     SET @@group_concat_max_len = 1024;
Comments