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.

CodePudding user response：

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