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

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?

mysqlsql


Solution 1:[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?

Solution 2:[2]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 p.marino
Solution 2 halfer

Related Questions