TSQL foreach query

Question

I am trying to create a random SSN for all the people in my database, however it is difficult for me to do this.

This is the query I'm using:

UPDATE tblTenant SET SSNO = ( SELECT CAST(AA as CHAR(1)) + CAST(AB as CHAR(1)) + CAST(AC as CHAR(1)) + '-' + CAST(AD as CHAR(1)) + CAST(AE as CHAR(1)) + '-' + CAST(AF as CHAR(1)) + CAST(AG as CHAR(1)) + CAST(AH as CHAR(1))+ CAST(AI as CHAR(1)) FROM (SELECT CAST(RAND() * 10 AS TINYINT) as A, CAST(RAND() * 10 AS TINYINT) as B, CAST(RAND() * 10 AS TINYINT) as C, CAST(RAND() * 10 AS TINYINT) as D, CAST(RAND() * 10 AS TINYINT) as E, CAST(RAND() * 10 AS TINYINT) as F, CAST(RAND() * 10 AS TINYINT) as G, CAST(RAND() * 10 AS TINYINT) as H, CAST(RAND() * 10 AS TINYINT) as I) as A ); 

It works correctly to generate a random SSN, however it only executes once, i.e. everyone gets the same SSN.

How can I make each user generate a new random SSN?

Answer 1

Look at this and this previous SO answer: How to create a random number for each row in TSQL Select?

Answer 2

Try the following:

 UPDATE tblTenant SET SSNO = RIGHT('000'+CAST(ABS((CAST(NEWID() AS VARBINARY))%(1000)) AS VARCHAR(3)),3)+'-'+ RIGHT('00'+CAST(ABS((CAST(NEWID() AS VARBINARY))%(100)) AS VARCHAR(2)),2)+'-'+ RIGHT('0000'+CAST(ABS((CAST(NEWID() AS VARBINARY))%(10000)) AS VARCHAR(4)),4)