Refresh a table using random values ​​from multiple values ​​in another table

Question

Consider this data:

CREATE TABLE #Data (DataID INT, Code VARCHAR(2), Prefix VARCHAR(3)) INSERT INTO #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 INTO #Prefix (Code, Prefix) VALUES ('AA', 'ABC') , ('AA', 'DEF') , ('AA', 'GHI') , ('AA', 'JKL') 

I want to set the Prefix value in #Data as a random Prefix from #Prefix with the appropriate Code .

Using the inner join direct just results in using a single value:

 UPDATE D SET Prefix = P.Prefix FROM #Data AS D INNER JOIN #Prefix AS P ON D.Code = P.Code 

From reading other questions here, NEWID() recommended as a way to randomly arrange something. Change join to:

 SELECT TOP 1 subquery ordering by NEWID() 

still selects only one value (albeit random each time) for each row:

 UPDATE D SET Prefix = (SELECT TOP 1 P.Prefix FROM #Prefix AS P WHERE P.Code = D.Code ORDER BY NEWID()) FROM #Data AS D 

So I'm not sure how to get a random prefix for each data record from one update statement. I could probably do some kind of loop through the #Data table, but I never touch loops in SQL , and I'm sure this will be slow. The actual application of this will be on tens of thousands of entries, with hundreds of prefixes for dozens of codes.

Answer 1

Here's how to do it:

 UPDATE d SET Prefix = ca.Prefix FROM #Data d CROSS APPLY(SELECT TOP 1 Prefix FROM #Prefix p WHERE d.DataID = d.DataID AND p.Code = d.Code ORDER BY NEWID()) ca 

Pay attention to d.DataID = d.DataID . This is necessary to force the Sql Server engine to revise the subquery for each row in the #Data table.