• Status: Solved
• Priority: Medium
• Security: Public
• Views: 285

# Update with random number

Hello,

I would like to randomize values in a table.
The table we call MainTable It has columns UserId, Username, ColM (int).

Then I have another table with the random values.
The table we call PoolTable. It has columns Id, PoolCol (int)

I would like to find random values for each UserId. I would like to replace ColM with the value of PoolCol.

Example:
PoolTable
Id=1, PoolCol=55
Id=2, PoolCol=73
Id=3, PoolCol=32

MainTable

One example of result could be

How is best to do this?
0
johnkainn
• 2
• 2
1 Solution

Commented:
update u
set colm = x.poolcol
from (select x.*,row_number() over (order by newid()) as rn
from maintable as x) as U
inner join pooltable as x
on x.id=u.rn
where u.colm<>x.poolcol

one method use the windowing function over to calculate a random row_number for each row in the main table by using newid()  to achieve a random order to the rows....
then join the row number to the id of the pool table and update...
0

Author Commented:
Thank you. How would I modify this so that when there are more items in MainTable then in PoolTable each user would get a random item from the Pool table. (It could also be the other way around, i.e. more items in pool table then in MainTable).
I created temporary table so you can run this. I added 2 names to the Maintable . Below is also the result that comes when I run it so you understand what I mean.

``````DECLARE @MainTable table(UserId int, Username varchar(50),ColM int)

DECLARE @PoolTable table(Id int, PoolCol int)
INSERT INTO @PoolTable(Id,PoolCol)values(1,55)
INSERT INTO @PoolTable(Id,PoolCol)values(2,73)
INSERT INTO @PoolTable(Id,PoolCol)values(3,32)

update u
set colm = x.poolcol
from (select x.*,row_number() over (order by newid()) as rn
from @MainTable as x) as U
inner join @PoolTable as x
on x.id=u.rn
where u.colm<>x.poolcol

SELECT * FROM @MainTable
``````

The result if I run this is

1      John      5
2      Bill      73
3      Howard      32
4      Michael      55
5      Anne      5
6      Mary      5

You see that the random number is only updated for 3 users.
0

Commented:
a simple method would be to use the modulus of the number of rows in the pool table to conform the ranges...

update u
set colm = x.poolcol
from (select x.*
,((row_number() over (order by newid()))
% ((select count(*) from @pooltable) - 1))+1 as rn
from @MainTable as x) as U
inner join @PoolTable as x
on x.id=u.rn
where u.colm<>x.poolcol

so rn generates numbers in the range 1 - number of rows in @pooltable

you may need to go a row_number() over (order by id) as rid  on the pooltable
and join rn to rid if there is the possibility that the pooltable ids would be sequential from 1..
0

Author Commented:
Thank you very much:-)
0

## Featured Post

• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.