July 28, 2008 at 6:28 am
Hi all,
I am looking to include a random number generator in an update query (basically simulating a dice roll to decide if a field is updated or not), but unsure if it's even possible to do this.
What I have is two tables as follows:
[font="Courier New"]tblPeople:
Name CatA CatB CatC CatD RNG
---- ---- ---- ---- ---- ---
Person1 4 5 2 3 4
Person2 3 2 4 4 3
Person3 5 3 1 5 5
...
tblUpdates:
Name Cat
----- ---
Person1 CatA
Person2 CatC
Person3 CatB
...[/font]
I'm then running this query:
update tblPeople
set CatA = (case when Cat = 'CatA' then CatA+1 else CatA end)
from dbo.tblPeople INNER JOIN
dbo.tblUpdates ON dbo.tblPeople.Name = dbo.tblUpdates.Name;
update tblPeople
set CatB = (case when Cat = 'CatB' then CatB+1 else CatB end)
from dbo.tblPeople INNER JOIN
dbo.tblUpdates ON dbo.tblPeople.Name = dbo.tblUpdates.Name;
update tblPeople
set CatC = (case when Cat = 'CatC' then CatC+1 else CatC end)
from dbo.tblPeople INNER JOIN
dbo.tblUpdates ON dbo.tblPeople.Name = dbo.tblUpdates.Name;
update tblPeople
set CatD = (case when Cat = 'CatD' then CatD+1 else CatD end)
from dbo.tblPeople INNER JOIN
dbo.tblUpdates ON dbo.tblPeople.Name = dbo.tblUpdates.Name;
I know it's not the most elegant, but it does what I need it to do. But what I would also like to throw into the mix is a random number generator which will return a value between 1 and 6, and then if the number is equal to or less than the value in the RNG column, then perform the update on the relevant column for that person, otherwise don't. I would need the number generation to be repeated for each person on the tblUpdate table.
I suspect I'm going about this entirely wrong, so I throw myself upon the mercy of the more expert. 🙂
July 28, 2008 at 6:46 am
Hi ,
This seemed to work for me:
SELECT
CatA = (CASE WHEN (Cat = 'CatA') AND
(CAST(RAND(CAST(NEWID() AS VARBINARY))*6+1 AS INT) >= RNG) THEN CatA+1 ELSE CatA END),
CatB = (CASE WHEN (Cat = 'CatB') AND
(CAST(RAND(CAST(NEWID() AS VARBINARY))*6+1 AS INT) >= RNG) THEN CatB+1 ELSE CatB END),
CatC = (CASE WHEN (Cat = 'CatC') AND
(CAST(RAND(CAST(NEWID() AS VARBINARY))*6+1 AS INT) >= RNG) THEN CatC+1 ELSE CatC END),
CatD = (CASE WHEN (Cat = 'CatD') AND
(CAST(RAND(CAST(NEWID() AS VARBINARY))*6+1 AS INT) >= RNG) THEN CatD+1 ELSE CatD END)
FROM dbo.tblPeople p
INNER JOIN dbo.tblUpdates u ON p.Name = u.Name
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
July 28, 2008 at 7:34 am
Awesome, worked like a charm! Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply