-1

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

GMB
  • 216,147
  • 25
  • 84
  • 135
Francesco Marcucci
  • 3
  • 3
  • I removed the SQL Server tag, since you obviously are running MySQL. Please always tag just one database. – GMB Dec 17 '20 at 10:55

1 Answers1

0

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.

GMB
  • 216,147
  • 25
  • 84
  • 135
  • I tried the solution you suggested but I get this error" #1583 - Incorrect parameters in the call to native function 'concat_ws'" – Francesco Marcucci Dec 17 '20 at 10:55
  • @FrancescoMarcucci: a comma was missing. Fixed. – GMB Dec 17 '20 at 10:56
  • I've modified the code ad follow: 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` ; – Francesco Marcucci Dec 17 '20 at 10:59
  • after every new submission, I get a string with NULL values – Francesco Marcucci Dec 21 '20 at 11:02