How can I set a variable while doing an Update statement? I can't seem to figure out the syntax.
So I want something like this below but it's saying the syntax is wrong:
SET @tempVariable := 0;
UPDATE myTable SET col1 = 5, col2 = @tempVariable, @tempVariable := 100;
MySQL Update Command Syntax The basic syntax of the Update query in MySQL is as shown below. UPDATE `table_name` is the command that tells MySQL to update the data in a table . SET `column_name` = `new_value' are the names and values of the fields to be affected by the update query.
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.
Description. := Assign a value. = Assign a value (as part of a SET statement, or as part of the SET clause in an UPDATE statement)
MySQL variable assignment. There are two ways to assign a value to a user-defined variable. The first way is to use the SET statement as follows: SET @variable_name := value; 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 syntax starts with the keyword “UPDATE”, thereby informing the MySQL Server about the type of activity to be performed. This is a mandatory keyword and cannot be omitted. Next comes the name of the table on which the update action has to be performed. This is mandatory and cannot be omitted. Third, is again a keyword – SET.
Using MySQL UPDATE to modify values in multiple columns. To update values in the multiple columns, you need to specify the assignments in the SET clause. For example, the following statement updates both last name and email columns of employee number 1056:
MySQL system variables holds global or session level values, these variables are used to configure various operations. You can set values to these variables dynamically using the SET statement.
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:
ALTER TABLE Proj ADD col3 numeric;
SET @tempVariable := 0;
UPDATE myTable SET col1 = 5, col2 = @tempVariable, col3 = @tempVariable := 100;
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;
The key is the ":=" operators. MySQL User Variable
You can also assign a value to a user variable in statements other than SET. In this case, the assignment operator must be := and not = because the latter is treated as the comparison operator = in non-SET statements:
1 Use the one of the updating column
SET @tempVariable := 0;
UPDATE myTable
SET col1 = 5,
col2 = @tempVariable := 100,
col3 = @tempVariable := col2 + 1;
@tempVariable is always 100 and col3 will be always 101. Seems mySQL will use the new assigned value instead of original value in the table. This is different from MS SQL. To make it more clear, try the following example, the value will be 1001 for col3 and @tempVariable.
UPDATE myTable
SET col1 = 5,
col2 = @tempVariable := 100,
col2 = 1000
col3 = @tempVariable := col2 + 1;
2 Use other column in the table than the updating column.
UPDATE myTable
SET col1 = 5,
col2 = @tempVariable := 100,
col3 = @tempVariable := col4 + 1;
@tempVariable and col3 will have the same value. They will be the col4 original value + 1.
I tested a similar query using a select and it worked for me, so I would rewrite your query as follows
SET @tempVariable := 0;
UPDATE myTable SET col1 = 5, col2 = (SELECT @tempVariable + 100);
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