Geek Answers Handbook

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

SQL SERVER 2000:

I have a table with test data (about 100,000 rows), I want to update a column value from another table with some random data from another table. According to this question , here 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 still has the same value for all strings; Any ideas what I'm doing wrong?

[ EDIT ] I expect this query to return a single value for each row, but it is not:

 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
+4
source share
2 answers

Your problem: you select only a single value and then update all columns with one single value.

To really get randomized, you need to take a step-by-step / looping approach - I tried this in SQL Server 2008, but I think it should work in SQL Server 2000 too:

 -- 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
+4
source

you need to ensure that each row is evaluated when choosing new identifiers.

it would do the trick

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

More articles:


All Articles