Declaring variables

[aalnaif]

I know this is really simple, but I've been suck on this for 2 hrs and I can't figure it out. I'm getting a syntax error when declaring a variable within an UPDATE statement. Here's how I'm declaring it:

DECLARE cnt int
SET cnt = 1

I'm using MySQL.

 

[rac2]

Well I dont think that you can declare a variable within an UPDATE statement. You could declare a user-defined variable in a procedure and use it in an UPDATE statement.

DECLARE @cnt INT
SET @cnt = 1

UPDATE MyTable SET
  some_column = @cnt
WHERE table_id = @cnt

 

[hvass]

You DECLARE variables you use in stored proceedures and functions but DECLARE is not part of an update statements syntax.

Why dont you post your code so we can see what you are on about.

 

[aalnaif]

OK, I tried making a procedure and declaring the variable outside of the UPDATE statement, but I'm still getting syntax errors related to declaring and setting the variable. Here is my code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `procA`.`tableA`$$

CREATE PROCEDURE `procA`.`tableA`()
BEGIN
DECLARE cnt int
Update tableA
Set visit_type =
SET cnt = 1
WHILE (cnt < 5) -- largest country code is 4 digits
BEGIN
case when (count(select location from main where SUBSTRING(pid,4,cnt)) = 1) then
(select location from main where SUBSTRING(pid,4,cnt))
cnt = 5
else
cnt = cnt + 1
END
END
END
where isnull(visit_type);
END$$

DELIMITER ;