Replacing a single column with randomly selected values from another table-CodePudding

I found some solutions to replace (below example) #test.col2 with data from #test2.src. But in the result it just selects a single random value and replaces them all with it. How to fix? Thanks!

#test (the target table)

col1    col2
-------------
A       1
B       2
C       3
D       4
E       5

#test2 (the source table)

src1
sample1
sample2
sample3

Query:

UPDATE #test
SET col1 = data1.LastName
FROM #test
CROSS APPLY
    (SELECT TOP(1) #test2.LastName
     FROM #test2
     ORDER BY NEWID()) data1

Example result:

col1    col2
----------------
A       sample2
B       sample2
C       sample2
D       sample2
E       sample2

CodePudding user response：

Here is one way to tackle this. It is using ROW_NUMBER in a cte to "randomize" the values.

if OBJECT_ID('tempdb..#test') is not null
    drop table #test;

create table #test
(
    col1 varchar(20)
    , col2 int
);
insert #test
select 'A', 1 union all
select 'B', 2 union all
select 'C', 3 union all
select 'D', 4 union all
select 'E', 5;

if OBJECT_ID('tempdb..#test2') is not null
    drop table #test2;

create table #test2
(
    LastName varchar(20)
);

insert #test2
select 'src1' union all
select 'sample1' union all
select 'sample2' union all
select 'sample3';

--here is the data before any updates
select * from #test;

with t1 as
(
    select col1
        , col2
        , RowNum = ROW_NUMBER() over(order by newid())
    from #test
)
, t2 as
(
    select LastName
    , RowNum = ROW_NUMBER() over(order by newid())
    from #test2
)

update t
set col1 = t2.LastName
from t1 
join t2 on t1.RowNum = t2.RowNum
join #test t on t.col1 = t1.col1

--we now have updated with a "random" row
select * from #test;