0

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?

1
  • Nope. Although you left out the parts where you pray that they didn't shove a UID into varchar typed field, or other shenanigans they could have done with it. Going to be fun dealing with all those FK references though... Jul 29, 2020 at 2:16

1 Answer 1

0

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

Question: Why?

The whole point on using an arbitrary, sequential identifier is that they should never change them over the entire lifetime of a record, from the point they're created until they are finally deleted. If you do find you need to change them, then something is badly wrong with the way you're using them.

Also, don't query the "largest" value to find out the id value that MySQL generated - it may not work correctly in a multi-user environment. Use the provided LAST_INSERT_ID() function instead:

INSERT INTO Users( name ) 
VALUES( 'Jim Bob' ); 

INSERT INTO Tasks( name, userId ) 
VALUES( 'Some task', LAST_INSERT_ID() );   <-- gets the last inserted User
2
  • Thanks, LAST_INSERT_ID() is not valid in SQL Server AFAIK. I only used it to assist with reproducing this issue; only used for creation of the tables, nowhere else. The issue comes into play when a user is merged with another user (it doesn't have to be a user, but I used it for this sample - maybe a better example is a company merger). Basically, we need to change the records to make user 1 and user 2 the same user. The easiest way we thought of is to change the id everywhere it's found. Restructuring the db to have parentId for merged rows would be best, but not possible in our case.
    – ctwheels
    Jul 29, 2020 at 15:37
  • (Sorry; Too many fora, too many DBMS flavours ...) SCOPE_IDENTITY is the Sql Server equivalent. If you have foreign keys everywhere, then updating keys is really (and deliberately) difficult. It may be easier to add a new, third identity and then merge the two, old identifiers into that new one with [a ton of] insert .. selects.
    – Phill W.
    Jul 30, 2020 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.