Execute same query in several tables

Question

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.

You need dynamic SQL, info [here:](http://stackoverflow.com/a/5728155/2144048) — JohnHC, Aug 31 '16 at 08:20
i need to get the table names in mysql and for that table execute the same query. is this possible using only mysql to get the table names and a loop to execute the query in mysql? — bcg, Aug 31 '16 at 08:38

Akshey Bhat · Accepted Answer · 2016-08-31T12:47:22.110

1

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;

edited Aug 31 '16 at 12:47

answered Aug 31 '16 at 09:57

Akshey Bhat

8,227
1
20
20

thanks for the response but when i execute the code mysql tells me: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL' at line 1 – bcg Aug 31 '16 at 12:15
SELECT Table_NAME FROM information_schema.tables WHERE TABLE_NAME LIKE 'w_extra_content%' run this query and check if you are getting any rows – Akshey Bhat Aug 31 '16 at 12:29
yes, the query returns the following rows: w_extra_content, w_extra_content_gl, w_extra_content_nd – bcg Aug 31 '16 at 12:35
consulta SQL: PREPARE stmt FROM @query; MySQL ha dicho: Documentación #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update w_extra_content set nombre = 'example' where nombre = 'old_example'' at line 1 – bcg Aug 31 '16 at 12:38
modified quiery please check – Akshey Bhat Aug 31 '16 at 12:48
the problem is the char (') before update. how can i delete this char. if i copy the query and delete the ' near update works but i don't know how can i do this here : (SELECT GROUP_CONCAT(CONCAT_WS(' ','update ',TABLE_NAME,' set nombre = \'example\' where nombre = \'old_example\'') – bcg Aug 31 '16 at 13:01
Did you run my updated query? If still the problem persists write select @query before prepare stmt and run up to this select. Copy query from output and run it – Akshey Bhat Aug 31 '16 at 13:25
i can't run the query that you posted, mysql tells that have an error on line 1, but if i copy (update w_extra_content set name= example where nombre = old_example) and i execute it works fine. i don't know where is the problem because i think is ok. thanks – bcg Sep 01 '16 at 07:07

Execute same query in several tables

1 Answers1