Tag: Update

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)

How to update a UID throughout the database?

Our database makes use of uniqueidentifier and we have the need to update that value everywhere it’s found in our system.

Sample minimal structure:

CREATE TABLE Users (     id uniqueidentifier not null default newsequentialid() primary key,     [name] nvarchar(100) not null )  CREATE TABLE Tasks (     id uniqueidentifier not null default newsequentialid() primary key,     [name] nvarchar(max) not null,     userId uniqueidentifier not null foreign key references Users(id) )  DECLARE @users TABLE(id uniqueidentifier) DECLARE @uid uniqueidentifier  INSERT INTO Users(name) OUTPUT INSERTED.id INTO @users VALUES('Jim Bob') SET @uid = (SELECT TOP 1 id FROM @users) INSERT INTO Tasks(name, userId) VALUES('Some task', @uid)

P.S. Obviously I have many more tables

I know how to search all database tables for a given value (I’ve used queries similar what’s written here this before). I’m unaware of a simpler solution for uid’s if any exist.

With the above, is there a simpler way than looping over tables, then columns of datatype uniqueidentifier, then locating the uid created in order to update its value to say 0x0?