Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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?
                Post exactly what you are trying to use, because I've created a new procedure and I don't get a syntax error using what you provided -- see my answer.
– OMG Ponies
                Oct 20, 2010 at 19:20
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()
                That seems to work!  The delimiter command is what I needed, apparently.  It's magic to me so far... let me look it up.
– Charles
                Oct 20, 2010 at 19:28
                > "DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.". Source: dev.mysql.com/doc/refman/8.0/en/declare.html However, SET works nearly everywhere, and CAST may be used for explicit types.
– Artfaith
                Sep 19, 2022 at 22:38
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;
                You will need a @ symbol to SET variables outside stored procedures/functions. e,g SET @F00 = '138'
– Yoga
                Dec 10, 2015 at 19:40
Looks like you forgot the @ in variable declaration. Also I remember having problems with SET in MySql a long time ago.
DECLARE @FOO varchar(7);
DECLARE @oldFOO varchar(7);
SELECT @FOO = '138';
SELECT @oldFOO = CONCAT('0', @FOO);
update mypermits 
   set person = @FOO 
 where person = @oldFOO;
                Sorry, I forgot to mention that the error remains when I put @s on the variables (as I originally had it.)
– Charles
                Oct 20, 2010 at 19:23
                I literally just copied and pasted it on a new query and it worked without a problem...do you have any code surrounding this portion?
– IWriteApps
                Oct 20, 2010 at 19:28
In Mysql, We can declare and use variables with set command like below
mysql> set @foo="manjeet";
mysql> select * from table where name = @foo;
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.