update table1
set firstname = (select top 1 firstname from table2 order by NEWID())
This just sets table1.firstname to the same value for all records. I know it's possible to do this, but everything I've seen online expects the same row count in both tables (or at least a greater amount in table1). I have 200,000 records in table1, I have 200 in table2. How can I set table1.firstname to a random value from table2.firstname when the row counts are off?
DECLARE @t1 TABLE (a INT)
DECLARE @t2 TABLE (b INT, c INT)
INSERT INTO @t1(a)
VALUES (0), (1), (2), (3), (4), (5)
INSERT INTO @t2(b)
VALUES (0), (1), (2)
UPDATE t2
SET c = t1.a
FROM @t2 t2
CROSS APPLY (
SELECT TOP(1) t1.a
FROM @t1 t1
WHERE t2.b IS NOT NULL -- any calculations for t2 columns
ORDER BY NEWID()
) t1
SELECT * FROM @t2
Output -
b c
----------- -----------
0 5
1 1
2 0
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments