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

random, sql-server, sql-server-2000, t-sql

Solution

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

Problem

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 ```

Original source

Related problems