Update column with 8 digit random number

How can I replace a field for each row in my table with a Randomly generated 8 number value The field is of type int that I'm updating

Answer this question

Update column with 8 digit random number

aabudara
OK, didn’t see that it has to be reseeded on a row base, so that’ll be your solution:

UPDATE MASTER

SET SSN = RIGHT(REPLICATE('0',8) + CONVERT(VARCHAR(100),CAST(RAND(CAST(NEWID() AS VARBINARY(128))) * 100000000 AS INT)),8)

select top 100 ssn from master

HTH, Jens Suessmeyer.
maranbe

If you need the syntax for doing that in a db2, then I guess you are in the wrong group. Try to find a db2 group and post the link with the description and the solution within your post there.

Sorry, but I think the most of the guys here are MSSQL geeks. :-)

HTH, Jens Suessmeyer.
mniccole
wow, crazy, I would have never guessed how to code that....I'm new with RAND
BirkanGA
Hi, try this here:

SELECT RIGHT(REPLICATE('0',8) + CONVERT(VARCHAR(100),CAST(RAND() * 100000000 AS INT)),8)

HTH, Jens Suessmeyer.
MobiTech
Hi,

I am looking for A similar kind of SQL which I need to use in DB2. I want to update a Alphanumeric DB2 column with some random number. Can someone please give me the UPDATE SQL. Thanks.
MrMartin
You basically have two issues ...

Getting your number and updating the field.

You could easily get your number by Round((10000000 * Rand()), 0)

which will return your 8 digit number .. between 0 and 10000000

but the problem with

UPDATE MASTER

SET SSN = RIGHT(REPLICATE('0',8) + CONVERT(VARCHAR(100),CAST(RAND() * 100000000 AS INT)),8)

select top 100 ssn from master

is that it is only getting the random number once and updating all the rows with the same value.

So Jens Suessmeyer is showing you a trick to get the number to regenerate every time by using the newid() function that returns an unique guid and turning that guid into an integer.

You probably could even just use ...

update YourTableName
set TheColNameToMakeRandom = ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT))
Rich Wilson - SCCP team
unfortunately, I get the same number repeated with your code all the way down:

UPDATE MASTER

SET SSN = RIGHT(REPLICATE('0',8) + CONVERT(VARCHAR(100),CAST(RAND() * 100000000 AS INT)),8)

select top 100 ssn from master

Results:

66081678
66081678
66081678
66081678
66081678

...
Jeffy John
Can you explain this whole reseeded to me I looked at bol and it barely explained it.
Update column with 8 digit random number

Answer this question