SQL random values

Hello all,

I am looking for the SQL statement that will update all the rows of my field
with random values between 30-100.
Thus, each record should be filled with a random value, but should not be
the same in all the records. It is ok if some records have the same values though.

Any ideas?
LVL 1
nectarios777Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
simply combine the code in the link above with the formula posted first + casting:

create view wrapped_rand_view
as
select rand( ) as random_value
go
create function wrapped_rand()
returns int
as
begin
declare @f float
declare @r int
set @f = (select random_value from wrapped_rand_view)
set @r = Round(((289) * @f + 10), 0)
return @r
end
go
update t set d = dbo.wrapped_rand()


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
random generic formula:
Round(((@Upper - @Lower -1) * Rand() + @Lower), 0)

in your case:
Round(((289) * Rand() + 10), 0)

0
 
nectarios777Author Commented:
Hello angelIII,

here is what I'm using:

update tbTable
set totalratings =
Round(((289) * Rand() + 10), 0)


The problem is that all records are being updated with the same value.
I want each record to have a different value.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:

update tbTable
set totalratings = ( select top 1
Round(((289) * Rand() + 10), 0)  from tbltable order by newid() )
0
 
nectarios777Author Commented:
Hello,

still the same problem
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
nectarios777Author Commented:
AngelIII, have you got any idea how to make it return integers >1 rather than decimals?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.