fill table A with random ID's from another table B

Question

I have two tables and I want update the column GebruikerID from table Topic with the ID of the table Gebruiker with random values.

You must also know the follow things:

• Topic.GebruikerID references to Gebruiker.ID.
• Not every ID is used in both tables.
• The random number that must be generated exist in the table Gebruiker (column ID) and must be updated into the table Topic (column GebruikerID).
• I use SQL server in an express edition.

Here are some images.

• The relation between the tables

  

• The ID's of the Gebruiker-table

  

Answer 1

You can assign two ROW_NUMBERs based on a random sort and join on them:

select
   t.id,
   g.id as gebruikerid
from
 (
   select id,
      (row_number() over (order by newid())
    % (select count(*) from gebruiker))+1 as rn -- number from 1 to n 
   from topic
 ) as t 
join 
 ( select id,
     row_number() over (order by newid()) as rn -- sequential number from 1 to n
   from gebruiker
 ) as g
  on t.rn = g.rn 
order by t.id

See fiddle

EDIT:

You can use the result of this SELECT to update the Topic table:

update t
set gebruikerid = dt.gebruikerid
from topic as t
join
 (
   select
      t.id,
      g.id as gebruikerid
   from
    (
      select id,
         (row_number() over (order by newid()) % (select count(*) from gebruiker))+1 as rn
      from topic
    ) as t 
   join 
    ( select id,
         row_number() over (order by newid()) as rn
      from gebruiker
    ) as g
   on t.rn = g.rn 
 ) as dt
on t.id = dt.id;

fiddle

Answer 2

Necromancing. The accepted answer will fail if the topic has only one entry, and gebruiker has only one entry.

You need to subtract -1 from row_number:

SELECT
     t.id
    ,g.id AS gebruikerid
FROM
(
    SELECT 
         id
        ,
        (
            (ROW_NUMBER() OVER (ORDER BY NEWID() ) -1) 
            % 
            (SELECT COUNT(*) FROM gebruiker) 
        ) + 1 AS rn -- number FROM 1 to n 
    FROM topic
) AS t 

INNER JOIN 
( 
    SELECT 
         id 
        ,ROW_NUMBER() OVER (ORDER BY NEWID() ) AS rn -- sequential number FROM 1 to n 
    FROM gebruiker 
) AS g 
    ON t.rn = g.rn 

ORDER BY t.id 

And then, you can update with a CTE:

;WITH CTE AS 
(
    SELECT
         t.id 
        ,t.GebruikerID 
        ,g.id AS gebruikerid
    FROM
    (
        SELECT 
             id
            ,GebruikerID 
            ,
            (
                (ROW_NUMBER() OVER (ORDER BY NEWID() ) -1) 
                % 
                (SELECT COUNT(*) FROM gebruiker) 
            ) + 1 AS rn -- number FROM 1 to n 
        FROM topic
    ) AS t 

    INNER JOIN 
    ( 
        SELECT 
             id 
            ,ROW_NUMBER() OVER (ORDER BY NEWID() ) AS rn -- sequential number FROM 1 to n 
        FROM gebruiker 
    ) AS g 
        ON t.rn = g.rn 
)
-- SELECT * FROM CTE 
UPDATE CTE SET GebruikerID = gebruikerid