Update table using random of multiple values in other 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 to be a random Prefix from #Prefix with a matching Code.

Using a straight inner join just results in one value being used:

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

From reading other questions on here, NEWID() is recommended as a way of randomly ordering something. Changing the join to:

SELECT TOP 1 subquery ordering by NEWID() 

still only selects a single value (though random each time) for every 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 entry from a single update statement. I could probably do some kind of loop through the #Data table, but I avoid ever touching loops in SQL and I'm sure that would be slow. The actual application of this will be on tens of thousands of records, with hundreds of prefixes for dozens of codes.

Answer 1

This is 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

Notice d.DataID = d.DataID. This is here to force Sql Server engine to reevaluate subquery for each row in #Data table.