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

I have a table that contains a column that has al开发者_开发百科l 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?

---

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

---

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.)