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;

Unfortunately I get this error:
"Parameter '@a' must be defined"

Is it possible to batch commands into 1 query like this, or do I need to create a stored procedure for this?

Answer 1

I think you need a stored procedure for any kind of statefullness. Is there a reason you have been reluctant to create one?

Also how are you running this code? Is it in an editor like SQL Server Manager or as a string in a program?

Answer 2

You placed the variable assignment in a wrong place:

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

Answer 3

Your query works fine for me. I tried running 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

Note that when running queries from MySQL Query Browser should enter one query per line, not two on one line as you are doing. If you want to put this in a stored procedure (probably 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();