Update table with random record in update statment

问题:

I have two tables. Table 1 has about 80 rows and Table 2 has about 10 million.

I would like to update all the rows in Table 2 with a random row from Table 1. I don't want the same row for all the rows. Is it possible to update Table 2 and have it randomly select a value for each row it is updating?

This is what I have tried, but it puts the same value in each row.

update member_info_test
set hostessid = (SELECT TOP 1 hostessId FROM hostess_test ORDER BY NEWID())

**Edited

回答1:

Ok, I think that this is one of the weirdest query that I've wrote, and I think that this is gonna be terrible slow. But give it a shot:

UPDATE A
SET A.hostessid = B.hostessId
FROM member_info_test A
CROSS APPLY (SELECT TOP 1 hostessId
             FROM hostess_test 
             WHERE A.somecolumn = A.somecolumn
             ORDER BY NEWID()) B

回答2:

I think this will work (at least, the with portion does):

with toupdate as (
      select (select top . . . hostessId from hostess_test where mit.hostessId = mit.hostessId order by newid()) as newval,
             mit.*
      from member_info_test mit
     )
update toupdate
    set hostessid = newval;

The key to this (and to Lamak's) is the outer correlation in the subquery. This is convincing the optimizer to actually run the query for each row. I don't know why this would work and the other version would not.

回答3:

Here is what i ended up using:

EnvelopeInformation would be your Table 2

PaymentAccountDropDown would be your Table 1 (in my case i had 3 items) - change 3 to 80 for your usecase.

;WITH cteTable1 AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY NEWID()) AS n,
        PaymentAccountDropDown_Id
    FROM EnvelopeInformation
    ),
cteTable2 AS (
    SELECT 
        ROW_NUMBER() OVER (ORDER BY NEWID()) AS n,
        t21.Id
    FROM PaymentAccountDropDown t21
    )
UPDATE cteTable1
   SET PaymentAccountDropDown_Id = (
       SELECT Id 
       FROM cteTable2
       WHERE  (cteTable1.n % 3) + 1 = cteTable2.n
)

reference: http://social.technet.microsoft.com/Forums/sqlserver/pt-BR/f58c3bf8-e6b7-4cf5-9466-7027164afdc0/updating-multiple-rows-with-random-values-from-another-table

回答4:

Update Table with Random fields

UPDATE p
    SET p.City= b.City
    FROM Person p
    CROSS APPLY (SELECT TOP 1 City
                 FROM z.CityStateZip 
                 WHERE p.SomeKey = p.SomeKey and -- ... the magic! ↓↓↓
                 Id = (Select ABS(Checksum(NewID()) % (Select count(*) from z.CityStateZip)))) b