MySql How to set a local variable in an update statement (Syntax?)

Question

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;

And how do you plan to use the value of @tempVariable after that? Executing this in a stored procedure would do the trick. — marco-fiset, Dec 12 '11 at 13:51
I edited the original question to show how I'd use it. I'm going to use it to update col3 to the temp variable. — Ray, Dec 12 '11 at 13:55
You can't. Your update statement is trying to set a variable = col2. it doesn't work that way you can update the table in an update statement not the variable. — xQbert, Dec 12 '11 at 13:57
@xQbert So there is no possible way to change a variable in an update statement? — Ray, Dec 12 '11 at 13:58

score 29 · Accepted Answer · answered Dec 12 '11 at 13:59

29

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

answered Dec 12 '11 at 13:59

ajreal

46,720
11
89
119

Perhaps add the `update` part, to clarify you're not using the stand alone `SET` statement. – Andomar Dec 12 '11 at 14:00
it won't work for procedure function, the variables is local declared – ajreal Sep 22 '13 at 10:52

kiquenet85 · Answer 2 · 2012-10-18T21:32:33.343

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;

This is actually bad practice. What if you table gets big and you have lots of indexes? It then will take a HUGE performance hit. — Roger, Feb 22 '16 at 22:44

score 5 · Answer 3 · answered Sep 12 '13 at 04:18

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.

score 1 · Answer 4 · edited Feb 17 '21 at 22:57

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

edited Feb 17 '21 at 22:57

Dharman

30,962
25
85
135

answered Jun 02 '17 at 14:54

nightsinwhiteaustin

11
1

2

This question was answered 6 years ago. What does your answer add to the already posted solution? – o-90 Jun 02 '17 at 15:11

will · Answer 5 · 2022-09-02T23:06:21.213

0

For more complex formulas, using the result column as a temporary store comes in handy:

UPDATE `myTable` join (SELECT @tempVariable := -1) a
SET `col2` = @tempVariable := @tempVariable + 1, `col2` = (SELECT 
CASE 
    WHEN MOD(@tempVariable,3) =0 THEN '1 of 3'
    WHEN MOD(@tempVariable,3) =1 THEN '2 of 3'
    WHEN MOD(@tempVariable,3) =2 THEN '3 of 3'
END 
)

edited Sep 02 '22 at 23:06

answered Sep 02 '22 at 22:59

will

153
3
6

score 0 · Answer 6 · edited May 04 '23 at 10:59

I managed to set multiple variables without using an extra temporary column by setting them within an IF() with multiple conditions wich all have to be evaluated and use that to set a random column (for example the PK) to itself.

For example, I need to set 3 variables within the UPDATE statement to split a string into 2 (combined with some conditions):

SET @trimmed='';
SET @firstPart='';
SET @firstSpace='';

I do that by setting id column to itself, wrapped inside an IF() where the variables are filled:

UPDATE `myTable`
SET 
  `id` = IF(
  ((@trimmed:=TRIM(`code_and_note`)) OR TRUE) AND 
  ((@firstSpace:=LOCATE(' ',@trimmed)) OR TRUE) AND 
  (@firstPart:=IF(@firstSpace=0, '', SUBSTRING(@trimmed, 1, @firstSpace))),
    `id`,
    `id`
  ),
  ...

You need to OR TRUE the assignments together else they won't be all assigned if one evaluates to FALSE.

Then you can use the variables in latter column assignments:

UPDATE `myTable`
SET 
  `id` = IF(...),
  `code` = IF(LENGTH(@trimmed) >= 10 AND @firstSpace = 0, @trimmed, IF(LENGTH(@firstPart) >= 10, @firstPart, '')),
  `note` = IF(LENGTH(@trimmed) >= 10 AND @firstSpace = 0, '', IF(LENGTH(@firstPart) >= 10, SUBSTRING(@trimmed, @firstSpace + 1), @trimmed));

See a working DB Fiddle here: https://www.db-fiddle.com/f/4UeH69PQdse1hUb9tyeGPc/0

MySql How to set a local variable in an update statement (Syntax?)

6 Answers6

Linked