UPDATE with variables to renumber column gives syntax error


Searching for a way to renumber a column within mysql, I’ve found multiple articles showing the same approach:

  • Renumbering an Ordering Field in MySQL
  • How to update a MySql column with ascending numbers

among others.

But trying it on my table I get a syntax error.

mysql> SET @rankStart = 10; mysql> SET @rankInc = 10; mysql> UPDATE fileFileTbl SET rank = (@rankStart := @rankStart + @rankInc) ORDER BY `rank` ASC; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank = (@rankStart := @rankStart + @rankInc) ORDER BY `rank` ASC' at line 1 

The command looks identical to the articles posted. What am I missing?

Here’s the table (with some columns omitted):

CREATE TABLE `fileFileTbl` (   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,   `fileEngineId` int(11) DEFAULT NULL,   `rank` int(11) DEFAULT '0',   `fileName` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT 'newfile',   PRIMARY KEY (`id`),   UNIQUE KEY `fileEngineId` (`fileEngineId`,`rank`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8; 

System is Centos 8, with mysql as:

# mysql --version mysql  Ver 8.0.17 for Linux on x86_64 (Source distribution)