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.

informatik01 · Accepted Answer · 2021-07-10 04:07:29Z

131

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;

edited Jul 10, 2021 at 4:07

informatik01

16.5k11 gold badges82 silver badges112 bronze badges

answered Sep 7, 2011 at 18:46

Tim Gautier

30.3k5 gold badges50 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matt Bannert Over a year ago

Indeed that is interesting. Added a MySQL workbench tag to the question – so others might find that this issue is related to MySQL Workbench.

GoYun.Info Over a year ago

I used MySQL workbench version 5.2.47 rev 10398 on Fedora 18 and there is not such problem with it.

Meloman Over a year ago

the second solution := will be deprecated. So prefer to use the SELECT ... INTO !

informatik01 · Accepted Answer · 2021-07-10 04:10:14Z

56

In the end a stored procedure was the solution for my problem.

Here´s what helped:

DELIMITER //

CREATE PROCEDURE test ()
BEGIN
  DECLARE myvar DOUBLE;
  SELECT somevalue INTO myvar FROM mytable WHERE uid = 1;

  SELECT myvar;
END//

DELIMITER ;

call test();

edited Jul 10, 2021 at 4:10

informatik01

16.5k11 gold badges82 silver badges112 bronze badges

answered Jun 19, 2010 at 11:57

Matt Bannert

28.4k40 gold badges145 silver badges209 bronze badges

1 Comment

Matt Bannert Over a year ago

Thx for the testing... sounds interesting. Unfortunately I do not get the last sentence. Did you forget a word or two?

Geoduck · Accepted Answer · 2014-09-09 21:51:40Z

51

These answers don't cover very well MULTIPLE variables.

Doing the inline assignment in a stored procedure causes those results to ALSO be sent back in the resultset. That can be confusing. To using the SELECT...INTO syntax with multiple variables you do:

SELECT a, b INTO @a, @b FROM mytable LIMIT 1;

The SELECT must return only 1 row, hence LIMIT 1, although that isn't always necessary.

answered Sep 9, 2014 at 21:51

Geoduck

9,0212 gold badges29 silver badges27 bronze badges

2 Comments

Anand Rockzz Over a year ago

I was trying to do the same, variables without @ also worked for me. Thanks

Geoduck Over a year ago

yes, in a stored procedure variables don't have to start with @. But, it is easier to demonstrate that way.

Somwang Souksavatd · Accepted Answer · 2014-10-13 02:33:44Z

29

You can also use SET instead of DECLARE

SET @myvar := (SELECT somevalue INTO myvar FROM mytable WHERE uid=1);

SELECT myvar;

answered Oct 13, 2014 at 2:33

Somwang Souksavatd

5,09734 silver badges30 bronze badges

2 Comments

RSK Over a year ago

This works and has clean syntax which I like

halliballi Over a year ago

it must be SELECT @myvar

Dan U. · Accepted Answer · 2010-06-19 10:26:59Z

15

Per the MySQL docs DECLARE works only at the start of a BEGIN...END block as in a stored program.

answered Jun 19, 2010 at 10:26

Dan U.

1,3573 gold badges12 silver badges19 bronze badges

1 Comment

pmiguelpinto90 Over a year ago

After googling for 'mysql select inside procedure' and arrived here, not having the declarations at the beginning is what was causing the syntax error, missing ; error for me.

Mike · Accepted Answer · 2010-06-19 10:36:23Z

13

You don't need to DECLARE a variable in MySQL. A variable's type is determined automatically when it is first assigned a value. Its type can be one of: integer, decimal, floating-point, binary or nonbinary string, or NULL value. See the User-Defined Variables documentation for more information:

http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

You can use SELECT ... INTO to assign columns to a variable:

http://dev.mysql.com/doc/refman/5.0/en/select-into-statement.html

Example:

mysql> SELECT 1 INTO @var;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @var;
+------+
| @var |
+------+
| 1    |
+------+
1 row in set (0.00 sec)

answered Jun 19, 2010 at 10:36

Mike

21.7k2 gold badges48 silver badges66 bronze badges

3 Comments

Matt Bannert Over a year ago

Hmm, somehow I am in trouble here.. SELECT 1 INTO @var; returns a syntax error too. so does: SELECT somevar INTO @var FROM mytable; Maybe it´s a DELIMITER problem?

Mike Over a year ago

What error are you receiving? What version of MySQL are you using?

Scott Willeke Over a year ago

Error Code: 1064. 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 'INTO @var' at line 3. Version=5.5.16

Tman · Accepted Answer · 2014-05-17 23:11:14Z

7

I am using version 6 (MySQL Workbench Community (GPL) for Windows version 6.0.9 revision 11421 build 1170) on Windows Vista. I have no problem with the following options. Probably they fixed it since these guys got the problems three years ago.

/* first option */
SELECT ID 
INTO @myvar 
FROM party 
WHERE Type = 'individual';

-- get the result
select @myvar;

/* second option */
SELECT @myvar:=ID
FROM party
WHERE Type = 'individual';


/* third option. The same as SQL Server does */
SELECT @myvar = ID FROM party WHERE Type = 'individual';

All option above give me a correct result.

answered May 17, 2014 at 23:11

Tman

811 silver badge1 bronze badge

Comments

b.b3rn4rd · Accepted Answer · 2014-01-30 03:05:01Z

4

It is worth noting that despite the fact that you can SELECT INTO global variables like:

SELECT ... INTO @XYZ ...

You can NOT use FETCH INTO global variables like:

FETCH ... INTO @XYZ

Looks like it's not a bug. I hope it will be helpful to someone...

answered Jan 30, 2014 at 3:05

b.b3rn4rd

8,8602 gold badges50 silver badges59 bronze badges

Comments

Derlin · Accepted Answer · 2018-02-14 12:43:30Z

4

For those running in such issues right now, just try to put an alias for the table, this should the trick, e.g:

SELECT myvalue 
  INTO myvar 
  FROM mytable x
 WHERE x.anothervalue = 1;

It worked for me.

Cheers.

edited Feb 14, 2018 at 12:43

Derlin

9,9112 gold badges34 silver badges57 bronze badges

answered Nov 30, 2016 at 10:23

emmanuel

411 bronze badge

Comments

HiMan · Accepted Answer · 2016-08-13 02:45:42Z

1

You maybe miss the @ symbol before your value,like that select 'test' INTO @myValue;

answered Aug 13, 2016 at 2:45

HiMan

112 bronze badges

1 Comment

Adam Fowler Over a year ago

That's a session variable, different topic

Mehrdad Masoumi · Accepted Answer · 2020-01-20 12:40:49Z

1

SELECT c1, c2, c3, ... INTO @v1, @v2, @v3,... FROM table_name WHERE condition;

answered Jan 20, 2020 at 12:40

Mehrdad Masoumi

4404 silver badges18 bronze badges

Collectives™ on Stack Overflow

SELECT INTO Variable in MySQL DECLARE causes syntax error?

11 Answers 11

3 Comments

1 Comment

2 Comments

2 Comments

1 Comment

3 Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

11 Answers 11

3 Comments

1 Comment

2 Comments

2 Comments

1 Comment

3 Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related