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.
