Samantha J Samantha J - 5 months ago 7
SQL Question

How can I update the value of a field in a table to be a random number that's unique most of the time?

I have a simple table:

CREATE TABLE [dbo].[Word] (
[WordId] INT IDENTITY (1, 1) NOT NULL,
[NameId] INT NOT NULL
PRIMARY KEY CLUSTERED ([WordId] ASC)
);


I have a unique index on NameId

I am trying to update this table and change one column to a random number:

UPDATE Word SET NameId = ROUND(RAND()*2147483647,0)


I realize there is a very very small chance this will not work but it's actually failing every time even though the table has only a very small number of rows the update always fails and says there's a duplicate.

Can anyone tell me what's happening here and also suggest a way to update this table so that there's no duplicate values of NameId created most of the time.

Answer

You are updating every NameId with same value, use WHERE statement to update only one row

EDIT: This should do the trick you are looking for, NewId() generates new id for each row

UPDATE Word SET NameId = abs(checksum(NewId()) % 2147483647)