CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlsql-server-2008
James123
James123

Reputation: 11652

update random numbers for top 100 rows in sql?

I need to update random numbers for top 100 rows (a field) in sql. random number should be less than 100. how to do that?

Upvotes: 5

Views: 5737

Answers (2)

Alex Aza
Alex Aza

Reputation: 78467

In SQL 2008

update top (100) MyTable
set MyField = cast(cast(crypt_gen_random(1) as int) * 100.0 / 256 as int)

I believe the same will work in SQL 2005.

[Edit]

If it doesn't work in SQL 2005, you can do this:

update top (100) MyTable
set MyField = abs(cast(newid() as binary(6)) % 100)

Upvotes: 12

Alex K.
Alex K.

Reputation: 175826

Pretty sure this is ok in 2k5;

--add rows numbers
;with ROWS(id, fld, rownum) as 
(
    select id, fld, 
    row_number() over (order by id asc) --this order defines your "top" 
    from tablename
)
update tablename
  set fld = 1 + abs(checksum(newid())) % 100 --dont use rand() as it will give the same value
  from ROWS inner join tablename on tablename.id = ROWS.id
  where rownum between 1 and 100 --limit to 100 rows

Upvotes: 0

Related Questions