MYSQL - How to update a table's headers with another table corresponding rows?

Question

I’d like to create a MYSQL procedure that dynamically renames the headers of TABLE01 with corresponding content of TABLE02 -> the expected results is shown on TABLE03.

Here are the tables content:

    TABLE01 (EXAMPLE, THE HEADERS MIGHT CHANGE BUT THEY ARE ALWAYS INCLUDED IN TABLE02):

            id1 | BOOK | AUTHOR | YEAR |
            1|Les Miserables|Hugo|1862|
            2|Notre Dame|Hugo|1831|
            |...|...|...|

    TABLE02 (STATIC TABLE THAT STAYS UNCHANGED):

            id2 | INPUT | OUTPUT | LANGUAGE |
            1|BOOK|livre |FR|
            2|AUTHOR|auteur|FR|
            3|YEAR|annee|FR|
            4|...|...|SP|
            5|...|...|SP|

The procedure should ask for a parameter (here 'FR'), then generate TABLE 03 with the corresponding content from TABLE 02 but with updated column headers.

         TABLE03 :
            id | livre | auteur | annee |
            1|Les Miserables|Hugo|1862|
            2|Notre Dame|Hugo|1831|  
            |...|...|...|

A simple UPDATE could work but I'd like to generalize for various tables with various columns.

Thanks for your help!

EDIT 1: I have reframes the question here : Dynamically updating a table's headers with MYSQL

If I followed you correctly, this would required dynamic SQL. — GMB, Oct 12 '19 at 17:39

score 1 · Answer 1 · answered Oct 13 '19 at 00:41

Disclaimer: This solution is untested

I think you need to use dynamic SQL which will build your SELECT statement and create the AS alias on the fly (the AS alias defines the column header name)

First, you need some reference tables (this assumes your database columns are always in English and will looked up against another language as neccesary)*

Language

id| LanguageName
1 | English
2 | Francais
3 | Espanola

Attribute

id|BaseName | Translation |LanguageId
1 |Book     | Livre       |2
2 |Book     | Libro       |3
3 |Author   | Auteur      |2
4 |Author   | Autora      |3
5 |Year     | Annee       |2
6 |Year     | Ano         |3

Then the bones of your stored procedure is something like

SET @language:='French'; # this will be a parameter in your stored procedure

SET @LangId = (SELECT id FROM Language WHERE LanguageName = @language) 

# Get the translation for the columns in your query
SET @BookTranslation:=(SELECT Translation FROM Attribute WHERE BaseName = 'Book' AND LanguageId = @LangId)
SET @AuthorTranslation:=(SELECT Translation FROM Attribute WHERE BaseName = 'Author' AND LanguageId = @LangId)
SET @YearTranslation:=(SELECT Translation FROM Attribute WHERE BaseName = 'Year' AND LanguageId = @LangId)


# build the statement
SET @sql:='SELECT '
SET @sql:=CONCAT(@sql,'Book AS `')
SET @sql:=CONCAT(@sql,@BookTranslation)
SET @sql:=CONCAT(@sql,'`,')
SET @sql:=CONCAT(@sql,'Author AS `')
SET @sql:=CONCAT(@sql,@AuthorTranslation)
SET @sql:=CONCAT(@sql,'`,')
SET @sql:=CONCAT(@sql,'Year AS `')
SET @sql:=CONCAT(@sql,@YearTranslation)
SET @sql:=CONCAT(@sql,'` ')

SET @sql:=CONCAT(@sql,'FROM TABLE01'

# Execute the statement
PREPARE dynamic_statement FROM @sql;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;

*You may also consider renaming the columns in your TABLE01 to ATTR1, ATTR2, ATTR3 and define the english attribute name in the Attirbute table, however this might make your database fairly difficult to work with

Thank you very much for this answer, but it seems that you are hardcoding the name of the header while these names will change depending on the topic. — SamanthaAlexandria, Oct 13 '19 at 00:47
So how will your stored procedure know "what" it has to translate? — SE1986, Oct 13 '19 at 00:51
I am thinking there could be a way to fetch the headers of TABLE 01 and then look the mapping in TABLE 02 (cf. renamed columns INPUT> OUTPUT). — SamanthaAlexandria, Oct 13 '19 at 00:55
But from your previous comment it looks like you need this to be dynamic (IE perform the column name lookup for a number of queries) there needs to a way for the sp to know which columns it has to translate, or will it always be queries against only TABLE01? — SE1986, Oct 13 '19 at 08:53
https://stackoverflow.com/a/929430/7031900 This may be a helpful approach — SE1986, Oct 13 '19 at 08:56
The above article is quite confusing. TABLE02 is a static table. I think I will reframe my question since it is not answered. — SamanthaAlexandria, Oct 13 '19 at 20:04
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/200785/discussion-between-samanthaalexandria-and-searle1986). — SamanthaAlexandria, Oct 13 '19 at 20:04

MYSQL - How to update a table's headers with another table corresponding rows?

1 Answers1