mysql - how to select all tables with column name and update that column -

i want find tables in db contain column name foo, , update value 0, thinking , dont know how place update on code, plan on having statement on events inside mysql database, im using wamp, idea having event run daily sets 'foo' columns 0 without me having manually

select table_name, column_name information_schema.columns column_name 'foo' 

no, not in single statement.

to names of tables contain column named foo:

select table_schema, table_name   information_schema.columns    column_name = 'foo' 

then, you'd need update statement each table. (it's possible update multiple tables in single statement, need (unnecessary) cross join.) it's better each table separately.

you use dynamic sql execute update statements in mysql stored program (e.g. procedure)

  declare sql varchar(2000);   set sql = 'update db.tbl set foo = 0';   prepare stmt sql;   execute stmt;   deallocate stmt; 

---

if declare cursor select information_schema.tables, can use cursor loop process dynamic update statement each table_name returned.

  declare done tinyint(1) default false;   declare sql  varchar(2000);    declare csr   select concat('update `',c.table_schema,'`.`',c.table_name,'` set `foo` = 0') sql     information_schema.columns c    c.column_name = 'foo'      , c.table_schema not in ('mysql','information_schema','performance_schema');   declare continue handler not found set done = true;    open csr;   do_foo: loop      fetch csr sql;      if done         leave do_foo;      end if;      prepare stmt sql;      execute stmt;      deallocate prepare stmt;   end loop do_foo;   close csr; 

(this rough outline of example, not syntax checked or tested.)

followup

some brief notes ideas glossed on in answer above.

to names of tables containing column foo, can run query information_schema.columns table. (that's 1 of tables provided in mysql information_schema database.)

because may have tables in multiple databases, table_name not sufficient identify table; need know database table in. rather mucking "use db" statement before run update, can reference table update db.mytable set foo....

we can use our query of information_schema.columns go ahead , string (concatenate) parts need create update statement, , have select return actual statements we'd need run update column foo, this:

update `mydatabase`.`mytable` set `foo` = 0  

but want substitute in values table_schema , table_name in place of mydatabase , mytable. if run select

select 'update `mydatabase`.`mytable` set `foo` = 0' sql 

that returns single row, containing single column (the column happens named sql, name of column isn't important us). value of column string. string happens (we hope) sql statement run.

we'd same thing if broke string pieces, , used concat string them us, e.g.

select concat('update `','mydatabase','`.`','mytable','` set `foo` = 0') sql 

we can use query model statement want run against information_schema.columns. we'll replace 'mydatabase' , 'mytable' references columns information_schema.columns table give database , table_name.

select concat('update `',c.table_schema,'`.`',c.table_name,'` set `foo` = 0') sql   information_schema.columns   c.column_name = 'foo' 

there databases not want update... mysql, information_schema, performance_schema. either need whitelist databases containing table want update

  , c.table_schema in ('mydatabase','anotherdatabase') 

-or- need blacklist databases not want update

  , c.table_schema not in ('mysql','information_schema','performance_schema') 

we can run query (we add order by if want rows returned in particular order) , list containing statements want run. if saved set of strings plain text file (excluding header row , formatting), adding semicolon @ end of each line, we'd have file execute mysql> command line client.

(if of above confusing, let me know.)

---

the next part little more complicated. rest of deals alternative saving output select plain text file, , executin statements mysql command line client.

mysql provides facility/feature allows execute any string sql statement, in context of mysql stored program (for example, stored procedure. feature we're going use called dynamic sql.

to use dynamic sql, use statements prepare, execute , deallocate prepare. (the deallocate isn't strictly necessary, mysql cleanup if don't use it, think it's practice anyway.)

again, dynamic sql available only in context of mysql stored program. this, need have string containing sql statement want execute. simple example, let's had this:

declare str varchar(2000); set str = 'update mytable set mycol = 0 mycol < 0'; 

to contents of str evaluated , executed sql statement, basic outline is:

prepare stmt str; execute stmt; deallocate prepare stmt; 

the next complicated part putting query running string value want execute sql statements. that, put cursor loop. basic outline take our select statement:

select bah humbug 

and turn cursor definition:

declare mycursor select bah humbug ; 

what want execute , loop through rows returns. execute statement , prepare resultset, "open" cursor

open mycursor;  

when we're finished it, we're goin issue "close", release resultset, mysql server knows don't need anymore, , can cleanup, , free resources allocated that.

close mycursor; 

but, before close cursor, want "loop" through resultset, fetching each row, , row. statement use next row resultset procedure variable is:

fetch mycursor some_variable; 

before can fetch rows variables, need define variables, e.g.

declare some_variable varchar(2000);  

since our cursor (select statement) returning single column, need 1 variable. if had more columns, we'd need variable each column.

eventually, we'll have fetched last row result set. when attempt fetch next one, mysql going throw error.

other programming languages let while loop, , let fetch rows , exit loop when we've processed them all. mysql more arcane. loop:

mylabel: loop   -- end loop mylabel; 

that makes fine infinite loop, because loop doesn't have "exit". fortunately, mysql gives leave statement way exit loop. typically don't want exit loop first time enter it, there's conditional test use determine if we're done, , should exit loop, or we're not done, , should go around the loop again.

 mylabel: loop      -- useful      if some_condition           leave mylabel;      end if;  end loop mylabel; 

in our case, want loop through of rows in resultset, we're going put fetch first statement inside loop (the useful want do).

to linkage between error mysql throws when attempt fetch past last row in result set, , conditional test have determine if should leave...

mysql provides way define continue handler (some statement want performed) when error thrown...

 declare continue handler not found  

the action want perform set variable true.

 set done = true; 

before can run set, need define variable:

 declare done tinyint(1) default false; 

with we, can change our loop test whether done variable set true, exit condition, our loop looks this:

 mylabel: loop      fetch mycursor some_variable;      if done           leave mylabel;      end if;      -- row  end loop mylabel; 

the "do row" want take contents of some_variable , useful it. our cursor returning string want execute sql statement. , mysql gives dynamic sql feature can use that.

note: mysql has rules order of statements in procedure. example declare statement have come @ beginning. , think continue handler has last thing declared.

---

again: cursor , dynamic sql features available only in context of mysql stored program, such stored procedure. example gave above example of body of procedure.

to created stored procedure, need incorporated part of this:

delimiter $$  drop procedure if exists myproc $$  create procedure myproc  not deterministic modifies sql data begin     -- procedure body goes here  end$$  delimiter ; 

hopefully, explains example gave in little more detail.