在 SQL Server 的更新语句中使用随机记录更新表? [英] Update table with random record in update statment in SQL Server?
问题描述
我有两张桌子.Table 1
大约有 80 行,Table 2
大约有 1000 万行.
I have two tables. Table 1
has about 80 rows and Table 2
has about 10 million.
我想用 Table 1
中的随机行更新 Table 2
中的所有行.我不希望所有行都在同一行.是否可以更新 Table 2
并让它为正在更新的每一行随机选择一个值?
I would like to update all the rows in Table 2
with a random row from Table 1
. I don't want the same row for all the rows. Is it possible to update Table 2
and have it randomly select a value for each row it is updating?
这是我尝试过的,但它在每一行中放置了相同的值.
This is what I have tried, but it puts the same value in each row.
update member_info_test
set hostessid = (SELECT TOP 1 hostessId FROM hostess_test ORDER BY NEWID())
**已编辑
推荐答案
好吧,我认为这是我写过的最奇怪的查询之一,而且我认为这会非常慢.但是试一试:
Ok, I think that this is one of the weirdest query that I've wrote, and I think that this is gonna be terrible slow. But give it a shot:
UPDATE A
SET A.hostessid = B.hostessId
FROM member_info_test A
CROSS APPLY (SELECT TOP 1 hostessId
FROM hostess_test
WHERE A.somecolumn = A.somecolumn
ORDER BY NEWID()) B
这篇关于在 SQL Server 的更新语句中使用随机记录更新表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!