Generating Random Numbers for Rows without Them

Question

I'm trying to set a 4-digit password between 1234 and 9999 for those rows that don't already have a password.

My first attempt uses the following code.

begin tran

DECLARE @Lower   int
DECLARE @Upper   int
SET @Lower = 1234 -- Lowest password value
SET @Upper = 9999 -- Highest password value

update DestinationComparisons
set Password = ROUND(((@Upper - @Lower - 1) * RAND() + @Lower), 0)
where Password = '' or Password is null

commit tran
go

However, this seems to give me the same 4 digits for every row that is updated. Obviously, I'd like a different password for each row.

Can someone help me see what I'm missing?

rand() is evaluated only ONCE per query, e.g. `select rand(), somefield from sometable` will always return the same rand value for each row. You'd have to do something like `select rand(id + time())` or whatever to force a new seed for each row. — Marc B, Apr 02 '13 at 21:53
I have a suggestion if you are able to accept letters and numbers. — Jack Pettinger, Apr 02 '13 at 21:55

Gordon Linoff · Answer 1 · 2013-04-02T22:02:26.680

2

Here is one possibility:

with toupdate as (
      select dc.*,
             (ROW_NUMBER() over (order by newid()) / cast(COUNT(*) over () as float)) as randnum
      from DestinationComparisons dc
     )
update toupdate
    set Password = ROUND(((@Upper - @Lower - 1) * randum + @Lower), 0)
    where Password = '' or Password is null

It uses newid() to generate a random row number, then divides that by teh count to get a value between 0 and 1. This can then be used for the password calculation.

edited Apr 02 '13 at 22:02

answered Apr 02 '13 at 21:56

Gordon Linoff

1,242,037
58
646
786

Getting a syntax error. Still looking... – Jonathan Wood Apr 02 '13 at 22:00
1

@usr: What part of "Still looking..." are you confused about? – Jonathan Wood Apr 02 '13 at 22:03
This code gets me the error "Incorrect syntax near the keyword 'as'" and I'm not sure I see why. – Jonathan Wood Apr 02 '13 at 22:07
@JonathanWood . . . What version of SQL Server are you using? This will work on versions 2005 and greater (and should work on most versions of Sybase). – Gordon Linoff Apr 02 '13 at 22:08
@GordonLinoff: I'm using 2008 R2. It might be me. Sorry, I'm not that familiar with *WITH* syntax. I pasted your code right after my `begin tran` line. – Jonathan Wood Apr 02 '13 at 22:12

ljh · Accepted Answer · 2013-04-02T23:14:49.780

2

begin tran
update DestinationComparisons
set Password = cast(left(cast(ABS(checksum(newid())) as varchar),4) as int)
where Password = '' or Password is null
commit tran

edited Apr 02 '13 at 23:14

answered Apr 02 '13 at 22:03

ljh

2,546
1
14
20

Very interesting. Still checking it out. – Jonathan Wood Apr 02 '13 at 22:04
Actually, the second version can product 3-digit passwords. – Jonathan Wood Apr 02 '13 at 22:45
Where are the upper and lower bounders? – gotqn Apr 03 '13 at 05:55

Generating Random Numbers for Rows without Them

2 Answers2