Search code examples
mysqlsqlalter-tablegenerated-columns

SQL query with ALTER TABLE print error #1064 in query execution

I've built an SQL query in Aruba MySQL database using the alter table Statement to generate a new column with comma-separated values. I'm trying to automate the concatenate function every time a new record is submitted into the database table. After I execute the query I always get a #1064 error.

ALTER TABLE 1459630_form_1
    ADD COLUMN 'datimarketing' 
        MEDIUMTEXT GENERATED ALWAYS AS (CONCAT(`segmento_auto_richiesto`,',',`alimentazione`,',',`km_annui_percorsi`,',',`modalit_di_acquisto`,',',`budget_di_spesa`,',',`rata_mensile`,',',`abitudini_di_acquisto`,',',`numero_di_componenti_del_nucleo_familiare`,',',`hobby`,',',`professione`,',',`iscritto_ad_associazioni_di_categoria`,',',`privacy_mkt_all`,',',`giorno_preferito_per_il_ricontatto`,',',`orario_preferito_per_il_ricontatto`)) STORED AFTER `orario_preferito_per_il_ricontatto`
        UPDATE TABLE(1459630_form_1)

Somebody could help me? Thanks

Solution

  • In MySQL the syntax goes like:

    ALTER TABLE 1459630_form_1
    ADD COLUMN datimarketing MEDIUMTEXT 
    GENERATED ALWAYS AS (CONCAT_WS(',', `segmento_auto_richiesto`, `alimentazione`, `km_annui_percorsi`)) STORED 
    AFTER `orario_preferito_per_il_ricontatto`
;

    I shorten the number of concatenated columns to make the code more readable (but the logic is the same of course).

    Rationale:

    • the column name should not be surrounded with single quotes

    • parentheses need to balance

    • UPDATE TABLE() is not applicable here

    Side note: CONCAT_WS() comes handy to shorten the CONCAT() expression.