3

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;

forpas
  • 160,666
  • 10
  • 38
  • 76
digitalniweb
  • 838
  • 1
  • 7
  • 15
  • Welcome to Stack Overflow. Please see https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question/271056#271056 for SQL related questions. – Progman Apr 25 '21 at 18:14
  • What are you trying to do with this query? Please [edit] your question to include the sample table with the rows you have and the resulting table you want from that. – Progman Apr 25 '21 at 18:40
  • The query that you have does not return your expected results: https://www.db-fiddle.com/f/u1CjtXV6X3umMooYTz3U9k/0 – forpas Apr 25 '21 at 19:12
  • Will the original orders always be sequential? Or could one be missing? – Scratte Apr 25 '21 at 21:04
  • @forpas I edited the post to be correct... Because I don't care about the result... I just need to know how to rewrite the code so it does the same thing but won't throw the warning. – digitalniweb Apr 26 '21 at 13:54
  • @Scratte I just need to know how to rewrite the code so it does the same thing but won't throw the warning. It is an easy for loop with incrementation += 1; – digitalniweb Apr 26 '21 at 13:56
  • @digitalniweb what is your current version of MySql? – forpas Apr 26 '21 at 13:57
  • @forpas MySQL 8.0.21 – digitalniweb Apr 26 '21 at 13:58

1 Answers1

5

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.

informatik01
  • 16,038
  • 10
  • 74
  • 104
forpas
  • 160,666
  • 10
  • 38
  • 76