Solved

# Update with random number

Posted on 2013-02-06
Medium Priority
278 Views
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
Question by:johnkainn
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 50

Expert Comment

ID: 38861990
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 Comment

ID: 38863422
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

LVL 50

Accepted Solution

ID: 38863931
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 Closing Comment

ID: 38876197
Thank you very much:-)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
###### Suggested Courses
Course of the Month8 days, 11 hours left to enroll