Sql-server – fill table A with random ID’s from another table B

randomsql server

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

Best Answer

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

Related Solutions

Sql-server – Unexpected results with random numbers and join types

By adding the additional SELECT it pushes the compute scalar evaluation deeper into the plan and gives the join predicate, the compute scalar at the top then references the earlier one.

SELECT rando.RandomNumber, d.database_id
FROM 
  (SELECT ( SELECT 1 + ABS(CHECKSUM(NEWID())) % (4)) AS RandomNumber 
   FROM sys.databases WHERE database_id <= 4) AS rando
INNER JOIN sys.databases d ON rando.RandomNumber = d.database_id

|--Compute Scalar(DEFINE:([Expr1071]=[Expr1070]))

|--Compute Scalar(DEFINE:([Expr1070]=(1)+abs(checksum(newid()))%(4)))

Still digging into just why it waits so late to do it, but currently reading this post by Paul White (https://sql.kiwi/2012/09/compute-scalars-expressions-and-execution-plan-performance.html). Perhaps it has something to do with the fact that NEWID is not deterministic?

Postgresql – Sampling in PostgreSQL

In Postgres 9.3+, you can use the folowing:

select t.*
from 
  generate_series(1, 10) as x(i)
    cross join lateral
  ( select *, x.i
    from Table_Name
    -- where random() < 0.01
    order by random()
    limit 1 
  ) ;

which basically chooses 1 random row, 10 times.

In older versions, you can use a simple cross join (no lateral):

select t.*
from 
    generate_series(1, 1000) as x(i)
  cross join 
    Table_Name as t
    -- where random() < 0.01
    order by random()
    limit 10
  ) t ;

which creates a 1000-fold copy of the table (so each row is there 1000 times) and then chooses 10 rows with the same method as your query. If the number of copies (1000) is large enough compared to the wanted rows (10), the probabilities are almost equal to the probabilities you would have got with replacement.

Performance of this second query will of course be horrible, even with small tables.

Best Answer

Related Solutions

Sql-server – Unexpected results with random numbers and join types

Postgresql – Sampling in PostgreSQL

Related Question