I have two tables, and in one table I need to set a column to equal to a random value listed in another table. Each row should randomly choose a value from a list and update itself as such. I tried this, and it will randomly select a value and set it, however EVERY row gets set to that one randomly selected value. I need each row to select another value. Rows can equal, that is fine, but they shouldn't all be the exact same value:
UPDATE dbo.Hardware Set Device = (SELECT top 1 name FROM dbo.device ORDER BY NewID())
When I run this, the nested select statement is only executed once and ALL values will be set to that specific value. How can I make it choose another value for each row?
