CODEWITHSUNDEEP
sql-server-2008sql-server-2008-r2
gina
gina

Reputation: 11

Generate unique 9 digits values

I have a table as shown below:

Group           
---------------------------     
Id  Num         Name    Age
---------------------------
1   424000000   Damine  22
2   324000000   Arshley 18
3   276000000   Tita    20
4   424000000   Helen   21
5   424000000   Mary    19
6   324000000   Kathe   20
7   324000000   Mark    18
8   276000000   Phill   22

i want to make the Num col unique so i need to generate 9 digit numeric random values for the duplicates.

please help, Thanks

Upvotes: 0

Views: 1782

Answers (3)

Qqbt
Qqbt

Reputation: 812

you set the num column as an identity field with a seed like 100000000 or just set the num field to an identity and print its value with an overloaded tostring method

Update: doing this (adding an identity column) through Management Studio will drop and recreate the table, which is not recommended on a very very large table

Upvotes: 3

Whiler
Whiler

Reputation: 8086

Not tested... but something like that...

SELECT 
    CONVERT(VARCHAR(9), RIGHT(NEWID(), 9)) AS [MyID] 
WHERE 
    LEFT([MyID], 1) NOT LIKE '0';

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280330

WITH u AS
(
 SELECT *, new_num = ROW_NUMBER() 
  OVER (PARTITION BY Num ORDER BY Id)
  FROM dbo.Group
)
UPDATE u SET Num += new_num - 1
FROM u
WHERE new_num > 1;

Upvotes: 1

Related Questions