Updating table with random data With NEWID() does not work

Question

SQL SERVER 2000:

I have a table with test data (about 100000 rows), I want to update a column value from another table with some random data from another table. According to this question, This is what I am trying:

UPDATE testdata
SET type = (SELECT TOP 1 id FROM testtypes ORDER BY CHECKSUM(NEWID()))

-- or even
UPDATE testdata
SET type = (SELECT TOP 1 id FROM testtypes ORDER BY NEWID())

However, the "type" field is still with the same value for all rows; Any ideas what Am I doing wrong?

[EDIT] I would expect this query to return one different value for each row, but it doesn't:

SELECT testdata.id, (SELECT TOP 1 id FROM testtypes ORDER BY CHECKSUM(NEWID())) type
FROM testdata

-- however seeding a rand value works
SELECT testdata.id, (SELECT TOP 1 id FROM testtypes ORDER BY CHECKSUM(NEWID()) + RAND(testdata.id)) type
FROM testdata

Answer 1

Your problem is: you are selecting only a single value and then updating all columns with that one single value.

In order to really get a randomization going, you need to do a step-by-step / looping approach - I tried this in SQL Server 2008, but I think it should work in SQL Server 2000 as well:

-- declare a temporary TABLE variable in memory
DECLARE @Temporary TABLE (ID INT)

-- insert all your ID values (the PK) into that temporary table
INSERT INTO @Temporary SELECT ID FROM dbo.TestData

-- check to see we have the values
SELECT COUNT(*) AS 'Before the loop' FROM @Temporary 

-- pick an ID from the temporary table at random    
DECLARE @WorkID INT
SELECT TOP 1 @WorkID = ID FROM @Temporary ORDER BY NEWID()

WHILE @WorkID IS NOT NULL
BEGIN
    -- now update exactly one row in your base table with a new random value
    UPDATE dbo.TestData
    SET [type] = (SELECT TOP 1 id FROM dbo.TestTypes ORDER BY NEWID())
    WHERE ID = @WorkID

    -- remove that ID from the temporary table - has been updated 
    DELETE FROM @Temporary WHERE ID = @WorkID

    -- first set @WorkID back to NULL and then pick a new ID from 
    -- the temporary table at random        
    SET @WorkID = NULL
    SELECT TOP 1 @WorkID = ID FROM @Temporary ORDER BY NEWID()
END

-- check to see we have no more IDs left
SELECT COUNT(*) AS 'After the update loop' FROM @Temporary 

Answer 2

you need to enforce a per row calculation in the selection of the new ids ..

this would do the trick

UPDATE testdata
SET type = (SELECT TOP 1 id FROM testtypes ORDER BY outerTT*CHECKSUM(NEWID()))
FROM testtypes outerTT