MYSQL - Warning: #1287 Setting user variables within expressions is deprecated and will be removed in a future release

Warning: #1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.

This is what MySQL 8.0.21 spits out. The code works... But I need it to work in future versions too.... So my question is what is the correct approach to this warning?

I need a variable to be set to some number (which I will get via ID of a row). And I need it to be incremented by 1 with every SET statement.

How should I change this code so the warning would go away?

SET @fromorder = (SELECT `order` FROM forms WHERE id=5);
/* SET @fromorder = 4;  - so this is the same in the example below */
UPDATE forms SET `order` = @fromorder := @fromorder + 1 WHERE `order` > -1 and `order` <= 4 ORDER BY `order` ASC;
SET @fromorder = null;

The problem as I understand underlays in:

`order` = @fromorder := @fromorder + 1

This needs to be changed I guess?

So from this:

+-------+-----------+
|   id  |   order   |
+-------+-----------+
|   1   |   0       |
|   2   |   1       |
|   3   |   2       |
|   4   |   3       |
|   5   |   4       |
+-------+-----------+

I want to get this (for example) with this query:

+-------+-----------+
|   id  |   order   |
+-------+-----------+
|   1   |   5       |
|   2   |   6       |
|   3   |   7       |
|   4   |   8       |
|   5   |   9       |
+-------+-----------+

It is an easy for loop with incrementation += 1;

Solution

Your version of MySQL supports window functions and by using ROW_NUMBER() window function you can avoid variables:

UPDATE forms f
CROSS JOIN (SELECT `order` FROM forms WHERE id = 5) c
INNER JOIN (
  SELECT *, ROW_NUMBER() OVER (ORDER BY `order`) rn
  FROM forms
  WHERE `order` > -1 and `order` <= 4 
) t ON t.id = f.id
SET f.`order` = c.`order` + t.rn;

See the demo.