Search code examples
mysqlalter-tableinformation-schema

Can I rename a MySQL Column without knowing the existing column name?

I'm writing a series of MySQL queries to manipulate data in tables. I've got one table that is automatically generated by a system outside my control. When I get it, the table has several columns:

ID, Name, Date, Title and a column containing decimal values.

Each time the table is automatically generated by the external system, it names the value containing the decimal values with the current date. For now, the column is called "16_Jul_17", as that is the last day the system was run.

I need a reliable way to query the table (and rename the column), without needing to know the current name of the decimal value column. Is there a way to do one of the following options:

  1. Rename the decimal value column without knowing the name of the column
  2. I can use information_schema to get the name, but then how do I use that to rename the column
Solution

  • I've found a workaround that works for me. I don't know how applicable it is in general, but for my application, it works like a charm.
    Since I know the order of the columns in the table from the external source, I simply use a UNION ALL, as follows:

    Select 'Id', 'Name', 'Date', 'Title', 0.00 AS 'Value' LIMIT 0
UNION ALL
Select * from original_table

    MySQL uses the values as column names except where explicitly aliased. Note that the LIMIT 0 is required so that you don't have a dummy row.