SELECT INTO Variable in MySQL DECLARE causes syntax error?

Question

I´d like to SELECT a single value into a variable. I´d tried to following:

DECLARE myvar INT(4); 

-- immediately returns some syntax error.

SELECT myvalue    FROM mytable   WHERE anothervalue = 1; 

-- returns a single integer

SELECT myvalue    INTO myvar    FROM mytable   WHERE anothervalue = 1; 

-- does not work, also tried @myvar

Is possible to use DECLARE outside of stored procedures or functions?

Maybe I just dont get the concept of user variables... I just tried:

SELECT myvalue INTO @var FROM `mytable` WHERE uid = 1; SELECT @var; 

...which worked just like it´s supposed to. But if I run each query at a time i just get @var NULL.

Answer 1

I ran into this same issue, but I think I know what's causing the confusion. If you use MySQL Query Analyzer, you can do this just fine:

SELECT myvalue  INTO @myvar  FROM mytable  WHERE anothervalue = 1; 

However, if you put that same query in MySQL Workbench, it will throw a syntax error. I don't know why they would be different, but they are.

To work around the problem in MySQL Workbench, you can rewrite the query like this:

SELECT @myvar:=myvalue FROM mytable WHERE anothervalue = 1;