MySQL v8.0.13 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release

Question

Executing the following query in MySQL v8.0.13 results in this error:

0 row(s) affected, 1 warning(s): 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Please set variables in separate statements instead.

SET @i = -1;

UPDATE `tb_test`
SET `order` = (@i := @i + 1)
ORDER BY `order` ASC;

Any suggestions on how to set the variables in a separate statement?

I am also in the dark here, same use case, need to increment a variable inside an update. Please let me know if you found a solution. — Jean Pacher, Jan 23 '20 at 14:16
I have not found a MySQL-specific solution to this problem yet. I ended up doing the calculations in the my application code instead of the database. I'd prefer the MySQL solution, however my results are not too lengthy so it's minimal load in the app. — Philip Ferreira, Jan 23 '20 at 18:07
I'll keep digging around, will let you know if I find anything. — Jean Pacher, Jan 23 '20 at 18:30

score 1 · Answer 1 · answered Nov 26 '18 at 16:49

1

This is really a shot in the dark (never used mySQL), but checking the docs it says:

"Support for setting user variables in statements other than SET was deprecated in MySQL 8.0.13. This functionality is subject to removal in MySQL 9.0."

(Emphasis mine).

So maybe the problem is that you increment @i without using SET? Can you rewrite this with an explicit SET and see if it helps?

answered Nov 26 '18 at 16:49

p.marino

6,244
3
25
36

Thanks @p.marino. I've tried different methods of setting @i outside the UPDATE statement, however I can't seem to find a valid format. – Philip Ferreira Nov 26 '18 at 17:53

score 1 · Answer 2 · answered Feb 10 '23 at 18:24

My chosen solution was to use the Window functions as explained in https://stackoverflow.com/a/65305941

WITH
  ranking AS(
    SELECT
      `name`,
      RANK() OVER (
        ORDER BY `name` DESC
      ) ranking
    FROM
      `tb_test`
)
UPDATE
    `tb_test`,
    ranking r
SET
  `tb_test`.`order` = r.ranking - 1
WHERE
  `tb_test`.`name` = r.`name`
;

score 0 · Answer 3 · edited May 31 '22 at 21:24

I came across this post trying to resolve the same issue in my use case where I can't find a better way to assign a user variable within the same SELECT query that references further down the statement - and I've looked at leveraging CTEs that just doesn't work for me - but I think I might have a suggestion for yours:

SET @i = -1; UPDATE `tb_test` SET `order` = (@i + ROW_NUMBER()) ORDER BY `order` ASC;

If you came up with a different answer, I'd be curious to know how you resolved it for edification.

MySQL v8.0.13 Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release

3 Answers3