Clever Geek Handbook

Modify table using select select

I have two tables. one of which is the field information table and the other is the field data table.

by default all columns of the field data table are given as text fields of some kind (varchar, text, etc.)

what I would like to do is. Instead of creating an ALTER TABLE query manually:

ALTER TABLE `field_data` 
CHANGE COLUMN `field_id_2` `field_id_2` decimal NOT NULL, 
CHANGE COLUMN `field_id_3` `field_id_3` decimal NOT NULL,
CHANGE COLUMN `field_id_22` `field_id_22` decimal NOT NULL,
CHANGE COLUMN `field_id_23` `field_id_23` decimal NOT NULL,
CHANGE COLUMN `field_id_25` `field_id_25` decimal NOT NULL,
CHANGE COLUMN `field_id_27` `field_id_27` decimal NOT NULL,
CHANGE COLUMN `field_id_30` `field_id_30` decimal NOT NULL,
CHANGE COLUMN `field_id_66` `field_id_66` decimal NOT NULL,
CHANGE COLUMN `field_id_72` `field_id_72` decimal NOT NULL;

I would like to write a dynamic ALTER TABLE query based on the field_info table, now I am getting these field IDs by doing this query:

SELECT field_id FROM `field_info` WHERE `field_type` = 'a_decimal_field'

which returns the values: 2, 3, 22, 23, etc.

So I need a query (if possible) that modifies the table based on the sub selection, concatenating field_id_ into the result of each sub selection, and changes the type of that column to decimal.

I know I can do this in php by building a query, but if possible I am just looking for a pure mysql solution.

+3


source to share


1 answer 


SELECT CONCAT('ALTER TABLE `field_data` ', 
  GROUP_CONCAT(' CHANGE COLUMN `field_id_', field_id, '` ',
    ' `field_id_', field_id, '` DECIMAL NOT NULL')) 
FROM `field_info` 
WHERE `field_type` = 'a_decimal_field'
INTO @sql;

PREPARE stmt FROM @sql;

EXECUTE stmt;


+7


source






More articles:


All Articles