Mysql - use query results for another query

Question

I want to find some specific Columns of a database from Information_schema.Columns and edit those columns type. I don't know how can I use the result of my first query from Information_schema for making another query to edit columns. Also I have to do it in MySQL, not PHP.

I've read about Procedures a little , but it was confusing. Acutally I've never done much with mysql.

I would really appreciate if anyone can show me the path or any tutorial. thanks in advance

Answer 1

this could be what you are looking for. dynamic alter query:

SELECT CONCAT('ALTER TABLE `my_table` ', 
  GROUP_CONCAT(' CHANGE COLUMN `', COLUMN_NAME , '` ',
    ' `', COLUMN_NAME , '` DECIMAL (14,4)')) 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'my_db' AND TABLE_NAME = 'my_table'
INTO @sql;

PREPARE stmt FROM @sql;

EXECUTE stmt;

you can use a cursor in stored procedure to apply this to all tables.

  DECLARE curs CURSOR FOR  SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'my_db';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET bDone = 1;
  OPEN curs;

  SET bDone = 0;
  REPEAT
    FETCH curs INTO table_name;

    IF table_name
       /*alter query*/
    END IF;
  UNTIL bDone END REPEAT;

  CLOSE curs;