sql - Simple update statement so that all rows are assigned a different value

Question

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.

Answer 1

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)