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:

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)