I want to add a random value to a column in a query using T-SQL for Microsoft SQL Server 2008 R2. To do this, I use a subquery and pull a random record. Every time I run the query, it pulls a new random record, but the value for every row is identical.
How can I get a new random value for each row?
Sample query:
SELECT column1,
(SELECT TOP 1 column1 as c2 FROM Table2 ORDER BY NewID())
FROM Table1
Whereas table2 looks like
column1
value1
value2
value3
It always returns the same value for column2, instead of a new random record for each table.
First run:
column1, c2
1, value1
2, value1
3, value1
Second run:
column1, c2
1, value2
2, value2
3, value2
How can I get c2 (the subquery) to pull a new random record for each row?
Ideal scenario:
column1, c2
1, value2
2, value1
3, value2