如何在sql中随机更新列?

Question

我有一个表，我想随机更新这个表中的一些列。这是我的更新脚本

update personnels set 
first_name=(select top 1 first_name from personnels order by NEWID()),

我试着这个脚本,但从人员选择最高1 first_name order by NEWID()查询太花时间了。

如何有效地更新列?

Answer 1

尝试在外部生成Random序列并使用自连接进行更新。像这样

;WITH CTE
AS
(
SELECT
RN = ROW_NUMBER() OVER(ORDER BY NEW_ID()),
FristName
FROM personnels 
),C2
AS
(
SELECT
RN = ROW_NUMBER() OVER(ORDER BY NEW_ID()),
FristName
FROM personnels 
)
UPDATE C2
SET
FristName = CTE.FristName
FROM C2
INNER JOIN CTE
ON C2.RN = CTE.RN

Answer 2

我不确定你想要这个的目的是什么，但是尝试使用rand函数

update personnels 
set first_name =
(select first_name 
from personnels p
where id = (select rand(1 + (RAND() * max(id)))
from personnels p1))

我忘记了括号的计数，但是我认为可以运行

Answer 3

UPDATE table SET val =(SELECT val FROM table table1 WHERE col =(SELECT RAND(1+ (RAND() * MAX(col))))
FROM table table2()