I'd like to create a MYSQL stored procedure that dynamically renames the column names of TABLE_EXAMPLE with content from TABLE_MASTER -> the expected result is shown on TABLE_RESULT.
Here are the tables content:
TABLE_EXAMPLE (THE HEADERS MIGHT CHANGE BUT THEY ARE MAPPED WITH A COLUMN IN TABLE_EXAMPLE):
|header01 | header02|...|header n|
|data01 ..| data02..|...|data 0n|
|data11 ..| data12..|...|data 1n|
..........etc.................
|data n1..|data n2..|...|data nn|
TABLE_MASTER (STATIC TABLE, UNCHANGED):
|ORIGIN| TARGET| NAME|
|header01|header_master01|Paul|
|header02|header_master02|Paul|
..........etc.................
|header n|header_master n|Paul|
The expected result contains the data from TABLE_EXAMPLE but with mapped column names found via TABLE_MASTER.TARGET:
TABLE_RESULT:
|data_master01|data_master02|...|data_master0n| NAME|
|data01.......|data02.......|...|data 0n.......|Paul|
|data11.......|data12.......|...|data 1n.......|Paul|
.........................etc.........................
|data n1..|data n2...........|...|data nn.......|Paul|
PS: A simple: "ALTER TABLE table_example
CHANGE COLUMN old
new
char(250)", won't do.
Thanks for your help!
EDIT 1: I have tried to write this but without success because 'oldname' and 'newname' are not considered as variables.
BEGIN
DECLARE n INT(10) ; DECLARE i INT(10) ;
DECLARE oldname VARCHAR(40); DECLARE newname VARCHAR(40);
SET n=(SELECT count(id) FROM `master_table` where `name`='paul');
SET i=1; WHILE i<n DO
SET oldname=(SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE
`TABLE_SCHEMA`='mydb'AND `TABLE_NAME`='table_example' LIMIT 1, 1) ;
SET newname=(SELECT TARGET FROM MASTER_TABLE WHERE ORIGIN='oldname');
ALTER TABLE `table_example` CHANGE oldname newname VARCHAR(50) NOT NULL;
SET i=i+1 ; END WHILE ;
END