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