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

Question

I use this query to rewrite the id number column by date, after a new row is added to the database. Even if the query runs well I can't fix the error displayed at the end of the query. Any suggestion?

 SET @ROW = 0;
 UPDATE `mytable` SET `id` = @ROW := @ROW+1 ORDER BY `date` ASC;

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)'.

I tried to modify the query

 set id = "0";
 UPDATE `mytable` SET := id+1 ORDER BY `data` ASC;

with no success.

Yes, look up what the word "Deprecated" means! (TL:DR) ___To mark (a component of a software standard) as obsolete to warn against its use in the future so that it may be phased out.___ — RiggsFolly, Oct 30 '22 at 15:10
PS: I hope thats not an `id` column you may be using as a foreign key ??? — RiggsFolly, Oct 30 '22 at 15:13
Setting & reading the same user variable in the same select statement has always been explicitly undefined behaviour. Coding queries trying to do that is a faq. Debug questions require a [mre]. [ask] [Help] [Why the order of evaluation for expressions involving user variables is undefined?](https://stackoverflow.com/a/44751302/3404097) See my comments (re an incorrect answer) at [MySQL - Define a variable within select and use it within the same select](https://stackoverflow.com/a/16715618/3404097). — philipxy, Oct 30 '22 at 21:09

score 1 · Accepted Answer · edited Oct 30 '22 at 17:47

User variables are mostly superseded with window functions, available in MySQL 8.0.

You can what you ask for with row_number() and the update/join syntax :

update mytable t
inner join (select id, row_number() over(order by date, id) new_id from mytable) t1
  on t.id = t1.id
set t.id = t1.new_id

Demo on DB Fiddlde.

This assumes that id is a unique key to start with.

I would still question why you would need to alter what looks like a surrogate primary key. You can compute the row number on the fly in your queries in that's what you want, or use a view :

create view myview as
select t.*, row_number() over(order by date, id) new_id from mytable t

Demo on DB Fiddlde

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

1 Answers1