CODEWITHSUNDEEP
sqlsql-serverdatabase
Matt
Matt

Reputation: 61

How do I generate random numbers to fill in empty rows?

I've added two new columns on one of my database's table. They are called Exam and assignment results.

This is the table

At the moment the two columns are both empty. I want them filled with random examination results but no greater than 100. How to I generate random number for both columns from 0 to 100?

Thanks.

Upvotes: 2

Views: 1116

Answers (2)

Lucero
Lucero

Reputation: 60190

You can use the following expression (for instance as column) to generate random numbers from 0..100 (inclusive):

ABS(CAST(CAST(NEWID() AS BINARY(8)) as bigint))%101

The RAND() function without seed does not work as expected here because it is invoked only once per query, and thus you get one random number repeated.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can do this using rand(),checksum(), andnewid()`:

update t
    set exam = rand(checksum(newid())) * 101,
        assignment = rand(checksum(newid())) * 101;

In SQL Server, rand() is (essentially) called once for a query. To get around this, you can give it a new seed. You could also use other columns in each row as the seed value, if you wanted reproducible results.

Upvotes: 3

Related Questions