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?

newtover · Accepted Answer · 2010-04-02 09:49:12Z

5

You placed the variable assignment in a wrong place:

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

answered Apr 2, 2010 at 9:49

newtover

32.2k11 gold badges89 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joshua Jewell · Accepted Answer · 2010-03-31 20:58:06Z

2

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?

answered Mar 31, 2010 at 20:58

Joshua Jewell

3662 silver badges9 bronze badges

3 Comments

Jon Tackabury Over a year ago

Stored Procs in MySQL are a pain to manage, and to deploy. They require giving user accounts access to the mysql.proc table, and backing up and restoring a DB doesn't include the SP's. The SP support in MySQL really leaves a lot to be desired.

Joshua Jewell Over a year ago

It looks like you want to order the table from 0 - N. Is there a reason why you cannot use an identity column so that the data is incremented when inserted? Or is this existing data you cannot reload? I tried this in SQL Server: update tbl set sortId = 0 update tbl set sortId = ( select count(*) from tbl where sortId = 0 ) I was hoping the select would execute for each row and descend the values from N - 0 but it was only executed once.

Jon Tackabury Over a year ago

Unfortunately Joshua is correct, I do need to use a SP for this.

Mark Byers · Accepted Answer · 2010-04-02 20:48:25Z

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();

Collectives™ on Stack Overflow

Using MySQL variables in a query

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related