CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
Tripurari Yadav
Tripurari Yadav

Reputation: 216

SQL Server 2012 : update a row with unique number

I have a table with 50k records. Now I want to update one column of the table with a random number. The number should be 7 digits.

I don't want to do that with procedure or loop. 

PinDetailId  PinNo
--------------------
783          2722692
784          9888648
785          6215578
786          7917727

I have tried this code but not able to succeed. I need 7 digit number.

SELECT 
    FLOOR(ABS(CHECKSUM(NEWID())) / 2147483647.0 * 3 + 1) rn, 
    (FLOOR(2000 + RAND() * (3000 - 2000) )) AS rn2 
FROM
    [GeneratePinDetail]

enter image description here

Upvotes: 2

Views: 624

Answers (2)

charles-allen
charles-allen

Reputation: 4081

Random

For a random number, you can use ABS(CHECKSUM(NewId())) % range + lowerbound:
(source: How do I generate random number for each row in a TSQL Select?)

INSERT INTO ResultsTable (PinDetailId, PinNo)
SELECT PinDetailId, 
       (ABS(CHECKSUM(NewId())) % 1000000 + 1000000) AS `PinNo`
FROM GeneratePinDetail
ORDER BY PinDetailId ASC;

Likely Not Unique

I cannot guarantee these will be unique; but it should be evenly distributed (equal chance of any 7 digit number). If you want to check for duplicates you can run this:

SELECT PinDetailId, PinNo 
FROM ResultsTable result
INNER JOIN (
    SELECT PinNo
    FROM ResultsTable
    GROUP BY PinNo
    HAVING Count(1) > 1
) test
ON result.PinNo = test.PinNo;

Upvotes: 2

user681574
user681574

Reputation: 553

You can create a sequence object and update your fields - it should automatically increment per update.

https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql

Updated based on comment: After retrieving the 'next value for' in the sequence, you can do operations on it to randomize. The sequence can basically be used then to create a unique seed for your randomization function.

If you don't want to create a function yourself, SQL Server has the RAND function build in already.

https://learn.microsoft.com/en-us/sql/t-sql/functions/rand-transact-sql

Upvotes: 0

Related Questions