How can I update column randomly in sql?-CodePudding

I have a table and I want to update some columns in this table randomly. This is my update script

update personnels set 

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

I tried this script but select top 1 first_name from personnels order by NEWID() query took too long. How can I update column efficiantly random.

CodePudding user response：

Try generating a Random sequence outside and update using a self-join. Something like this

;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

CodePudding user response：

I'm not sure what possible purpose you would want this for but try using the rand function

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

i lost count on the brackets but think this is OK to run