[SOLVED] How to update table with values from another table not connected with the first one?

How to update table with values from another table not connected with the first one?

I have a table and want to replace newid() with values from another table. Here is an update statement:

UPDATE tbl1
SET tbl1.NewKey = NEWID()
FROM @List AS tbl1
WHERE tbl1.NewFlag = 1

I am going to update this table with values from my table instead of newid() :

drop TABLE if exists #tmpGUIDs
CREATE TABLE #tmpGUIDs
(
   rnum INT,
   uuid uniqueidentifier
)
-- filled here

How can I do this? As I see the problem is no keys to join these tables but here can be done something like "next value" from the tmp table

Solution

You can just fabricate a row number to update each qualifying row exactly once:

WITH c AS 
(
  SELECT NewKey, 
      rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM @List
   WHERE NewFlag = 1
)
UPDATE c SET c.NewKey = t.uuid
  FROM c
 INNER JOIN #tmpGUIDs AS t
    ON c.rn = t.rnum;

This only works fully if there are more rows in #tmpGUIDs than there are with NewFlag = 1.

fiddle