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:
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:
This is one of example of Inner Join vs Cross Apply.
Hope, It helps.
Awesome Solution
I’ve just tried this code in mssql2014. Unfortunately it generates the same ID’s for each row