2

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tripurari Yadav
  • 216
  • 1
  • 3
  • 11

2 Answers2

2

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;
charles-allen
  • 3,891
  • 2
  • 23
  • 35
  • I need to update unique/random number not continue series. as per your example, I am getting continue value 1000000,1000001,1000002.... – Tripurari Yadav Aug 19 '17 at 07:03
  • @TripurariYadav - Updated to be random... Unique and random are completely different things; ensuring both will be difficult without a recursive process. Are you sure you really need unique? That's not normal for a PIN/password; though it might be for an access token. – charles-allen Aug 19 '17 at 07:06
  • @ CodeConfident - fine, if it's not unique, but it should be a random number so that some one can not guess it. – Tripurari Yadav Aug 19 '17 at 07:09
  • @TripurariYadav - I added a query to check for duplicates... you might be able to eliminate the duplicates by regenerating just those rows. – charles-allen Aug 19 '17 at 07:11
0

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

user681574
  • 553
  • 2
  • 15