Declaring and using MySQL varchar variables

Question

I m trying to do some simple manipulations with variables in MySQL 5 0 but I can t quite get it to work   I ve seen many  very   different syntaxen for DECLARE SET  I m not sure why    in any case I m presumably confusing them picking the wrong one mixing them   Here s a minimal fragment that fails   DECLARE FOO varchar 7   DECLARE oldFOO varchar 7   SET FOO    138   SET oldFOO   CONCAT  0   FOO    update mypermits     set person   FOO   where person   oldFOO    I ve also tried wrapping it with BEGIN    END  and as a PROCEDURE   In this case MySQL Workbench helpfully tells me   SQL syntax error near      on the first line and  SQL syntax error near  DECLARE oldFOO varchar 7    on the second   Otherwise it gives both lines as errors in full  with  SQL syntax error near      on both   Edit  I forgot to mention that I ve tried it with and without  s on the variables   Some resources had it with  others without   What dumb mistake am I making

User · Answer

Looks like you forgot the   in variable declaration  Also I remember having problems with SET in MySql a long time ago   Try  DECLARE  FOO varchar 7   DECLARE  oldFOO varchar 7   SELECT  FOO    138   SELECT  oldFOO   CONCAT  0    FOO    update mypermits     set person    FOO   where person    oldFOO

User · Answer

try this   declare  foo    varchar 7            oldFoo varchar 7   set  foo    138  set  oldFoo    0     foo

User · Answer

In Mysql  We can declare and use variables with set command like below  mysql gt  set  foo  manjeet   mysql gt  select   from table where name    foo

User · Answer

If you are using phpmyadmin to add new routine then don t forget to wrap your code between BEGIN and END

User · Answer

I ran into the same problem using MySQL Workbench  According to the MySQL documentation  the DECLARE  statement declares local variables within stored programs    That apparently means it is only guaranteed to work with stored procedures functions   The solution for me was to simply remove the DECLARE statement  and introduce the variable in the SET statement   For your code that would mean   -- DECLARE FOO varchar 7    -- DECLARE oldFOO varchar 7    -- the   symbol is required SET  FOO    138    SET  oldFOO   CONCAT  0   FOO    UPDATE mypermits SET person   FOO WHERE person   oldFOO

User · Answer

Declare  variable type size    Set  variable    String  or Int     Example    Declare  id int   set  id   10    Declare  str char 50    set  str  Hello

User · Answer

This works fine for me using MySQL 5 1 35   DELIMITER     DROP PROCEDURE IF EXISTS  example   test     CREATE PROCEDURE  example   test     BEGIN    DECLARE FOO varchar 7     DECLARE oldFOO varchar 7     SET FOO    138     SET oldFOO   CONCAT  0   FOO      update mypermits      set person   FOO    where person   oldFOO   END     DELIMITER     Table   DROP TABLE IF EXISTS  example   mypermits   CREATE TABLE   example   mypermits       person  varchar 7  NOT NULL   ENGINE InnoDB DEFAULT CHARSET latin1   INSERT INTO mypermits VALUES   0138     CALL test

[sql] Declaring and using MySQL varchar variables

Examples related to sql

Examples related to mysql

Examples related to mysql-error-1064