Menu
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?
913
Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.
MySQL variable assignment There are two ways to assign a value to a user-defined variable. You can use either := or = as the assignment operator in the SET statement. For example, the statement assigns number 100 to the variable @counter. The second way to assign a value to a variable is to use the SELECT statement.
To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
The MySQL server maintains system variables that configure its operation. A system variable can have a global value that affects server operation as a whole, a session value that affects the current session, or both.
You placed the variable assignment in a wrong place:
SET @a:=0; UPDATE tbl SET @a:=sortId=@a+1 ORDER BY sortId;
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?
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
