sql - Update table using random of multiple values in other table -

consider data:

create table #data (dataid int, code varchar(2), prefix varchar(3))  insert #data (dataid, code) values (1, 'aa') , (2, 'aa') , (3, 'aa') , (4, 'aa') , (5, 'aa') , (6, 'aa')  create table #prefix (code varchar(2), prefix varchar(3))  insert #prefix (code, prefix) values ('aa', 'abc') , ('aa', 'def') , ('aa', 'ghi') , ('aa', 'jkl') 

i want set prefix value in #data random prefix #prefix matching code.

using straight inner join results in 1 value being used:

update d set prefix = p.prefix #data d inner join #prefix p on d.code = p.code 

from reading other questions on here, newid() recommended way of randomly ordering something. changing join to:

select top 1 subquery ordering newid()  

still selects single value (though random each time) every row:

update d set prefix = (select top 1 p.prefix #prefix p p.code = d.code order newid()) #data d 

so, i'm not sure how random prefix each data entry single update statement. kind of loop through #data table, avoid ever touching loops in sql , i'm sure slow. actual application of on tens of thousands of records, hundreds of prefixes dozens of codes.

this how it:

update d set prefix = ca.prefix #data d cross apply(select top 1 prefix              #prefix p              d.dataid = d.dataid , p.code = d.code order newid()) ca 

notice d.dataid = d.dataid. here force sql server engine reevaluate subquery each row in #data table.