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

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
Id=1, UserName="John", ColM=5
Id=2, UserName="Bill",ColM=5
Id=3, UserName="Howard", ColM=5
Id=4, UserName="Michael",ColM=5

One example of result could be
Id=1, UserName="John", ColM=73
Id=2, UserName="Bill",ColM=32
Id=3, UserName="Howard", ColM=55
Id=4, UserName="Michael",ColM=73

How is best to do this?
0
johnkainn
Asked:
johnkainn
  • 2
  • 2
1 Solution
 
LowfatspreadCommented:
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
 
johnkainnAuthor 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)
INSERT INTO @MainTable(UserId,Username,ColM)values(1,'John',5)
INSERT INTO @MainTable(UserId,Username,ColM)values(2,'Bill',5)
INSERT INTO @MainTable(UserId,Username,ColM)values(3,'Howard',5)
INSERT INTO @MainTable(UserId,Username,ColM)values(4,'Michael',5)
INSERT INTO @MainTable(UserId,Username,ColM)values(5,'Anne',5)
INSERT INTO @MainTable(UserId,Username,ColM)values(6,'Mary',5)

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

Open in new window


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
 
LowfatspreadCommented:
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
 
johnkainnAuthor Commented:
Thank you very much:-)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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