February 13th, 2014

SQL Server: Updating A Column With Random Lookup Values

This article explains how to update a table column with random values from another (lookup) table in SQL Server.

For example, Country table has two columns CountryID, CountryName and UserInfo table has CountryID column. We will update UserInfo table with random CountryIDs from Country table.

sql server table

SQL:

Here is the SQL script:


update UserInfo 
set UserInfo.CountryID = t2.CountryID
from UserInfo t1
cross apply  (
select top 1 CountryID
from Country
where t1.UserID = t1.UserID
order by newid()
) t2

Here, Cross Apply is used and yes… t1.UserID = t1.UserID is in where condition.

Output:

The above sql query returns following result with random values from Country table:

sql server table

This is one of example of Inner Join vs Cross Apply.

Hope, It helps.