Using MySQL variables in a query

Question

I am trying to use this MySQL query:

SET @a:=0; UPDATE tbl SET sortId=@a : =@a +1 ORDER BY sortId; 

Sorry, I get this error:
"@A" must be defined "

Is it possible to command commands in 1 request like this, or do I need to create a stored procedure for this?

Answer 1

I think you need a stored procedure for any statefullness state. Is there a reason you did not want to create it?

Also how do you use this code? Is it in an editor such as SQL Server Manager or as a string in a program?

Answer 2

You put the variable assignment in the wrong place:

 SET @a:=0; UPDATE tbl SET @a: =sortId=@a +1 ORDER BY sortId; 

Answer 3

Your request is great for me. I tried to launch it from MySQL Query Browser:

 CREATE TABLE tbl (Id INT NOT NULL, SortId INT NOT NULL); INSERT INTO tbl (Id, SortId) VALUES (1, 9), (2, 22), (3, 13); SET @a:=0; UPDATE tbl SET sortId=@a : =@a +1 ORDER BY sortId; SELECT * From tbl; 

Result:

 Id sortId 1 1 2 3 3 2 

Please note that when executing queries from MySQL Query Browser, one query per row should be entered, and not two on one row, as you do. If you want to put this in a stored procedure (maybe this is a good idea), you can create it like this:

 DELIMITER // CREATE PROCEDURE updateSortIds() BEGIN SET @a:=0; UPDATE tbl SET SortId=@a : =@a +1 ORDER BY SortId; END // DELIMITER ; 

And to execute it, use this:

 CALL updateSortIds();