How to declare a variable in MySQL-CodePudding

I should set VARIABLE in mysql query but not work: can you help me, where is the error?

SET @variabile = ( SELECT `submit_time` AS 'Submitted', 
                           max(if(`field_name`='N_PROGRESSIVO', `field_value`, null )) AS 'N_PROGRESSIVO', 
                           max(if(`field_name`='Submitted From', `field_value`, null )) AS 'Submitted From', 
                           GROUP_CONCAT(if(`file` is null or length(`file`) = 0, null, `field_name`)) AS 'fields_with_file' 
FROM `wpcust_cf7dbplugin_submits` 
WHERE `form_name` = 'Modulo SDO 2022 BS' 
GROUP BY `submit_time` 
ORDER BY `submit_time` DESC 
                ) as variabile);

CodePudding user response：

You have:

SET @variable = (SELECT ..) as variable);

But it should just be:

SET @variable = (SELECT ..);

Also, you will receive the error message (or similar)

Operand should contain 1 column(s)

Because the parser is right: your select returns 4 columns, but only one can be stored inside the variable. Change your query to only return a single column for a single variable.

CodePudding user response：

SELECT col1 INTO @variable1 FROM ... LIMIT 1
SELECT col1, col2 INTO @variable1, @variable2 FROM ... LIMIT 1

The number of variables must be the same as the number of columns or expressions in the select list. In addition, the query must returns zero or one row.

If the query return no rows, MySQL issues a warning of no data and the value of the variables remain unchanged.

In case the query returns multiple rows, MySQL issues an error. To ensure that the query always returns maximum one row, you use the LIMIT 1 clause to limit the result set to a single row.

Source https://dev.mysql.com/doc/refman/5.7/en/select-into.html https://www.mysqltutorial.org/mysql-select-into-variable/