Simple update statement so that all rows are assigned a different value
sql, sql-server
Solution
On some test table my end your original plan looks as follows.
It just calculates the result once and caches it in a sppol then replays that result. You could try the following so that SQL Server sees the subquery as correlated and in need of re-evaluating for each outer row.
UPDATE table1
SET table2Id = (SELECT TOP 1 table2Id
FROM table2
ORDER BY Newid(),
table1.table1Id)
For me that gives this plan without the spool.
It is important to correlate on a unique field from `table1` however so that even if a spool is added it must always be rebound rather than rewound (replaying the last result) as the correlation value will be different for each row.
If the tables are large this will be slow as work required is a product of the two table's rows (for each row in `table1` it needs to do a full scan of `table2`)
Problem
I'm trying to set a column in one table to a random foreign key for testing purposes. I attempted using the below query ``` update table1 set table2Id = (select top 1 table2Id from table2 order by NEWID()) ``` This will get one table2Id at random and assign it as the foreign key in table1 for each row. It's almost what I want, but I want each row to get a different table2Id value. I could do this by looping through the rows in table1, but I know there's a more concise way of doing it.