MySql How to set a local variable in an update statement (Syntax?)
This is possible :-
UPDATE myTable SET col1 = 5,
col2 = (@tempVariable:=@tempVariable+1) // to increment
To set an integer (not increment)
UPDATE myTable SET col1 = 5,
col2 = (@tempVariable:=100) // to assign any integer
If you want to obtain something like this:
SET @tempVariable := 0;
UPDATE myTable SET col1 = 5, col2 = @tempVariable, @tempVariable := 100;
You can do a trick like this:
- Create a column value.
ALTER TABLE Proj ADD col3 numeric;
- Give a value to col3 in order to set the variable you need (@tempVariable).
SET @tempVariable := 0;
UPDATE myTable SET col1 = 5, col2 = @tempVariable, col3 = @tempVariable := 100;
- Drop the col3
ALTER TABLE Proj DROP col3;
In this way, you can assign values to a variable without change attributes of a table. It is really usefull when setting dinamic values.
FOR EXAMPLE: @tempVariable := @otherVariable + 100;