2

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

4

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

  • Sorry, maybe I was not so clear but I will to update the column GebruikerID in Topic with random value. – H. Pauwelyn Oct 25 '15 at 15:51
  • @Hein: Of course you can use the result of this select to update your table, I'll add it to my answer. – dnoeth Oct 25 '15 at 17:06
  • Could you just order by newid() instead of doing a row_number over it? (Never mind, I re-read the question) – Greg Oct 26 '15 at 0:16
1

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 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.