CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Draco
Draco

Reputation: 16374

How to select random number from a set of numbers in SQL

I have a table that contains a column that has all NULL values. I would like to populate this column with a random number from a given set of numbers.

The set of given numbers will be generated from a SELECT statement that select these numbers from some other table.

E.G:

UPDATE tableA
SET someColumnName = SomeRandomNumberFromSet(SELECT number from tb_Numbers)

How do I accomplish this using MSSQL 2008?

Upvotes: 3

Views: 3961

Answers (2)

Martin Smith
Martin Smith

Reputation: 453920

The following isn't particularly efficient but works. The view is required to get around the "Invalid use of a side-effecting operator 'newid' within a function." error. The UDF is assumed to be non deterministic so will always be re-evaluated for each row.

This will avoid any problems with SQL Server adding spools to the plan and replaying earlier results.

If the number of rows to update (or numbers in the set) was much larger I wouldn't use this method.

CREATE VIEW dbo.OneNumber
AS
SELECT  TOP 1 number 
            FROM    master..spt_values 
            ORDER BY NEWID()

GO

CREATE FUNCTION dbo.PickNumber ()
RETURNS int
AS
BEGIN
    RETURN (SELECT number FROM dbo.OneNumber)
END

GO

DECLARE @tableA TABLE (someColumnName INTEGER)
INSERT INTO @tableA VALUES (2), (2), (2), (2), (2)

UPDATE  @tableA
SET     someColumnName = dbo.PickNumber()

SELECT * FROM @tableA

Upvotes: 4

MatBailie
MatBailie

Reputation: 86798

I asked a similar question a long time ago, and got a few different options.

Is this a good or bad way of generating random numbers for each record?


Once you can generate a random number from 1 to n, you can use it to choose the Xth irem from your list. (Easiest way is to have a sequential id on your set of legitimate values.)

Upvotes: 0

Related Questions