CODEWITHSUNDEEP
mysqlvariablesconcatenationvariable-assignment
Andrew Eisenberg
Andrew Eisenberg

Reputation: 137

SET a variable in SELECT statement - MySQL

I'm using this code which has an error:

SET @rejects = '';

SELECT *
FROM list
WHERE maker = 1
    AND by_ids IN ('10','11')
    AND country LIKE '%I%'
    AND (
        src IS NULL
        || src NOT IN (@rejects)
        AND checkSrc(src) = 'yes'
        AND SET @rejects = CONCAT(@rejects,',',src)
    );

What's causing the issue?

Upvotes: 12

Views: 68443

Answers (2)

Pacerier
Pacerier

Reputation: 89773

The issue is that you cannot mix select and set in one statement, there'll surely be syntax error:

select*from t where 1 and set@a=1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set@a=1' at line 1

If you want to do set within select, use the colon equals syntax. Change this:

select*from t where 1 and set@a=1;

into:

select*,@a:=1 from t where 1;

Here's how you update the variable upon each row:

create table t(id int); insert t values(1),(2),(3);
set@a=0;
select@a:=id from t;

+--------+
| @a:=id |
+--------+
|      1 |
|      2 |
|      3 |
+--------+

And you can even do concat:

set@a='0';
select @a:=concat(@a,',',id)from t;

+-----------------------+
| @a:=concat(@a,',',id) |
+-----------------------+
| 0,1                   |
| 0,1,2                 |
| 0,1,2,3               |
+-----------------------+

Or concat without the leading 0:

set@a='';
select @a:=concat(@a,if(@a='','',','),id)from t;

+------------------------------------+
| @a:=concat(@a,if(@a='','',','),id) |
+------------------------------------+
| 1                                  |
| 1,2                                |
| 1,2,3                              |
+------------------------------------+

However, the manual explicitly states that this is dangerous: link

...you should never assign a value to a user variable and read the value within the same statement...

...you might get the results you expect, but this is not guaranteed.

...the order of evaluation for expressions involving user variables is undefined.

This has also been mentioned on Xaprb.

Lastly, if you're doing quirky things like assigning differing value types to the variable and etc, checkout the manual to be sure you understand the intricate mechanisms.

Upvotes: 17

Ravindra Gullapalli
Ravindra Gullapalli

Reputation: 9188

Then you might write your query like this.

SET @rejects = '';
SELECT @rejects = CONCAT(@rejects,',',src) FROM list WHERE maker = 1 AND by_ids IN ('10','11') AND country LIKE '%I%' AND 
(src IS NULL OR src NOT IN (@rejects) AND checkSrc(src) = 'yes');
SELECT @rejects;

Upvotes: 2

Related Questions