-2

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
  • What are table headers? The column names? You should reconsider your database structure as you should not mix data with column names. – slaakso Oct 13 '19 at 20:05
  • Yes - table headers are column names. I precisely want to create a new table with headers from another table content. PS: header01 | header02| value may vary depending on which table_example I want to normalize. – SamanthaAlexandria Oct 13 '19 at 20:07
  • 1
    You should not mix data with database structure. If you describe what you are trying to achieve with this, you might get a proposal for better structure. – slaakso Oct 13 '19 at 20:13
  • Are you asking how to use `AS` to define column aliases of a query? Your question above is not clear. How do the example and master tables form the expected result? – Bill Karwin Oct 13 '19 at 20:13
  • @BillKarwin : Good point, I have just added the link/reasoning between the expected result and the previous tables in my question. – SamanthaAlexandria Oct 13 '19 at 20:17
  • So you basically want to (dynamically) rename the columns of the table according to a mapping defined in another table, is that correct? – GMB Oct 13 '19 at 20:33
  • Yes, exactly. I have titled my question this way. Thank you @GMB – SamanthaAlexandria Oct 13 '19 at 21:20

3 Answers3

2

I think I understand you want to select a column by name, and the names are strings in your TABLE_MASTER.

You can't do this in a single SQL query, because SQL cannot select a column by using a string expression. There's a difference between a string and an identifier. For example, this selects data from a column by identifier:

SELECT header01 ...

But the following is a string expression (a simple one, which is just a constant value). It returns only a fixed string 'header01', NOT the data from a column of that name:

SELECT 'header01' ...

Likewise, using any other expression in a select-list only selects the value of that expression, NOT the data stored in a column named by the string value of the expression.

Therefore if you want a query to return a dynamic column named by some other variable or expression, you can't do it in the same query where you read that expression. You have to format a new SQL query from the values you read. This is called a dynamic SQL statement (already mentioned by spencer7593, who posted an answer while I was writing my own answer).

You could use your TABLE_MASTER to format a dynamic SQL statement to fetch columns and redefine their alias:

SELECT CONCAT(
  'SELECT ', 
   GROUP_CONCAT(CONCAT(ORIGIN, ' AS ', TARGET)), ', ', 
   QUOTE(MAX(NAME)), ' AS NAME ',
  'FROM TABLE_EXAMPLE'
) INTO @sql
FROM TABLE_MASTER;

The result of this is a string that forms another SELECT statement, this one renames the columns as you want:

SELECT header01 AS header_master01,header02 AS header_master02, 'Paul' AS NAME FROM TABLE_EXAMPLE  

Then you can use the string stored in @sql as a dynamic SQL query.

Here's the procedure that does this:

DELIMITER ;;

CREATE PROCEDURE MyProc()
BEGIN
    SELECT CONCAT(
      'SELECT ', 
       GROUP_CONCAT(CONCAT(ORIGIN, ' AS ', TARGET)), ', ', 
       QUOTE(MAX(NAME)), ' AS NAME ',
      'FROM TABLE_EXAMPLE'
    ) INTO @sql
    FROM TABLE_MASTER;
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END

DELIMITER ;

Call the procedure, and get the result:

CALL MyProc();

+-----------------+-----------------+------+
| header_master01 | header_master02 | NAME |
+-----------------+-----------------+------+
| data01          | data02          | Paul |
| data11          | data12          | Paul |
+-----------------+-----------------+------+

I have to comment that this is a lot of trouble to go through. I would rather fetch the data as it is in the database, and reformat it in my application code. Then I wouldn't have to use any dynamic SQL to format the columns.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Your answer is very helpful and insightful. I feel ashamed to ask you but how can I create a new table from this? eg. I've tried unsuccessfully to add : CREATE TABLE `table03` AS ( [YOUR CODE] ). – SamanthaAlexandria Oct 13 '19 at 23:13
  • The same technique — you can format a string that contains a CREATE TABLE statement or an ALTER TABLE statement using the contents of your TABLE_MASTER. Then execute that string as a dynamic SQL statement. Sorry, I can't be more specific unless I understand what you are trying to do. – Bill Karwin Oct 13 '19 at 23:37
  • 2
    You have been asked multiple times to explain more clearly, but I guess you are not able to do that. Software engineering is a complex, difficult field (like any other type of engineering), and you need to practice your communication skills as much as your technical skills. – Bill Karwin Oct 13 '19 at 23:39
  • Hi Bill - Thank you for your guidance, I was able to add the CREATE TABLE statement using the same technique e.g. inside your code and generate the new table TABLE03 with the expected result. I am just a bit new with dynamic SQL. I need to study it more for sure to be more at ease. – SamanthaAlexandria Oct 14 '19 at 00:02
  • 1
    I agree that this should really be application side logic. It's makes for complicated database code when it's really presentation logic – SE1986 Oct 14 '19 at 08:13
1

The specification isn't entirely clear, what it is exactly we are trying to achieve.

Looks like we are wanting a stored procedure that returns a resultset.

That would mean the procedure would execute a SELECT statement.

It is possible for a SELECT statement to assign an alias to an expression returned in the SELECT list, which then becomes the column name in the resultset. For example

  SELECT 'some_expression' AS foo

The resultset returned by this SELECT statement will contain a column named foo.

The identifiers (table names, column names, function names) referenced in the SELECT statement are not dynamic; those cannot be modified dynamically in the execution of the statement. The alias to be assigned to the column must be specified in the statement

  SELECT some_expr AS new_column_name 

And have the value of new_column_name by dynamic, and retrieved from some table, we would need to run a separate SQL statement to retrieve the column name to be used.

Within the procedure, we can dynamically prepare the SQL text we want to execute.

As an example, with something like this in the body of a stored procedure :

  DECLARE col_name VARCHAR(80);

  SET col_name = 'foo' ;

  SET @sql = CONCAT('SELECT ''some_expr'' AS `',col_name,'`') ;

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;  

Executing the procedure would cause a statement like this to be executed:

  SELECT 'some_expr' AS `foo`

returning a resultset with a column name of foo.


Reference: https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • Please @spencer7593 see my edit and reconsider if your answer is still relevant. – SamanthaAlexandria Oct 13 '19 at 20:43
  • If we want to return a resultset from a procedure, then the procedure needs to execute a SELECT statement. If the intent is to change the column names in a table, then yes, we'd use an ALTER TABLE statement to rename the column. If we want to do that dynamically, then we'd probably need to retrieve the defintion of the column, not just the name i.e. in the general case, we can't assume that the datatype of the column is `VARCHAR(50)`. Having a procedure rename columns via ALTER TABLE in existing tables seems an ill-advised solution to whatever problem its intending to solve. – spencer7593 Oct 13 '19 at 20:52
  • and for clarity, note that that table columns have a name, we typically refer to that identifier as "column name". We don't call it a "header". – spencer7593 Oct 13 '19 at 20:54
  • Ok - thank you. I thought it was clear but maybe not. I have updated the question :) – SamanthaAlexandria Oct 13 '19 at 21:02
1

Here is a stored procedure for the purpose of renaming the columns of table table_example according to the mapping defined in table_master. It works by fetching the content of the mapping, and then dynamically generate a series of ALTER TABLE ... RENAME COLUMN ... TO ... commands (note that this syntax is only supported in SQL 8.0).

Obviously this is a one-shot procedure, since once it completes, the column are renamed.

You uncomment the SELECT @q statement and comment the three following lines to execute the procedure and display the rename commands without actually running them.

DELIMITER $$
CREATE PROCEDURE RenameColumns()
BEGIN

    DECLARE finished INTEGER DEFAULT 0;
    DECLARE old_col VARCHAR(50);
    DECLARE new_col VARCHAR(50);

    DECLARE curs CURSOR FOR SELECT origin, target FROM table_master;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN curs;
    renameLoop: LOOP

        FETCH curs INTO old_col, new_col;
        IF finished = 1 THEN 
            LEAVE renameLoop;
        END IF;

        SET @q = CONCAT('ALTER TABLE table_example RENAME COLUMN ', old_col, ' TO ', new_col);
        -- SELECT @q;
        PREPARE stmt FROM @q;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt; 

    END LOOP renameLoop;
    CLOSE curs;

END$$
DELIMITER ;

Demo on DB Fiddle

select * from table_master;

| origin   | target          |
| -------- | --------------- |
| header01 | header_master01 |
| header02 | header_master02 |
| header03 | header_master03 |


select * from table_example;

| header01 | header02 | header03 |
| -------- | -------- | -------- |
| 1        | 2        | 3        |


CALL RenameColumns();

select * from table_example;

| header_master01 | header_master02 | header_master03 |
| --------------- | --------------- | --------------- |
| 1               | 2               | 3               |
GMB
  • 216,147
  • 25
  • 84
  • 135
  • My version of MYSQL accepts this syntax: ALTER TABLE `table` CHANGE old new varchar(40); therefore I've replaced by: ('ALTER TABLE tabletestbis CHANGE ', old_col , new_col, 'VARCHAR(40)'); but for some reason it does not work. – SamanthaAlexandria Oct 13 '19 at 23:11
  • @SamanthaAlexandria: you have a missing space before the datatype: `CONCAT('ALTER TABLE tabletestbis CHANGE ', old_col , new_col, ' VARCHAR(40)')` – GMB Oct 13 '19 at 23:16
  • Made the change but get this: MySQL said: #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 'VARCHAR(40)' at line 1 – SamanthaAlexandria Oct 13 '19 at 23:20
  • 1
    @SamanthaAlexandria: my bad, it should be: `CONCAT('ALTER TABLE tabletestbis CHANGE COLUMN ', old_col , new_col, ' VARCHAR(40)')` – GMB Oct 13 '19 at 23:21
  • 1
    @SamanthaAlexandria: this is the correct syntax to change a column name. You can try and run this statement directly, like: `ALTER TABLE tabletestbis CHANGE COLUMN old_column new_column varchar(40)`. Or you can show the generated statements (as explained in my answer), and run them directly. – GMB Oct 13 '19 at 23:29
  • Correct, this is the right syntax but I still get: MySQL said: #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 'VARCHAR(40)' at line 1 with: `SET @q = CONCAT('ALTER TABLE tabletestbis CHANGE COLUMN ', old_col , new_col, ' VARCHAR(40)');` – SamanthaAlexandria Oct 14 '19 at 00:05
  • 1
    You need a space between the column names: `CONCAT('ALTER TABLE tabletestbis CHANGE COLUMN ', old_col , ' ', new_col, ' VARCHAR(40)')` – GMB Oct 14 '19 at 00:10
  • 1
    Hurray - it works. And this is another way of achieving the same results - it completes Bill's answer. Thank you to both of you. – SamanthaAlexandria Oct 14 '19 at 00:12