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