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

User · Answer

You maybe miss the   symbol before your value like that select  test  INTO  myValue

User · Answer

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

User · Answer

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 gt  SELECT 1 INTO  var  Query OK  1 row affected  0 00 sec   mysql gt  SELECT  var   ------     var    ------    1       ------  1 row in set  0 00 sec

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can also use SET instead of DECLARE  SET  myvar     SELECT somevalue INTO myvar FROM mytable WHERE uid 1    SELECT myvar

User · Answer

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

User · Answer

SELECT      c1  c2  c3      INTO       v1   v2   v3     FROM      table name WHERE      condition

[mysql] SELECT INTO Variable in MySQL DECLARE causes syntax error?

Examples related to mysql

Examples related to select

Examples related to mysql-workbench

Examples related to declare