sql-server - 使用 NEWID() 使用随机数据更新表不起作用

Question

SQL Server 2000:

我有一个包含测试数据的表(大约 100000 行)，我想使用另一个表中的一些随机数据更新另一个表中的列值。根据this question ，这就是我正在尝试的:

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

但是，所有行的“type”字段仍然具有相同的值；有什么想法我做错了什么吗？

[编辑]我希望此查询为每一行返回一个不同的值，但事实并非如此:

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

您的问题是:您仅选择一个单个值，然后使用该单个值更新所有列。

为了真正实现随机化，您需要采用逐步/循环方法 - 我在 SQL Server 2008 中尝试过此方法，但我认为它也应该在 SQL Server 2000 中工作:

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