select random between 2 numbers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

select random between 2 numbers

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-02-07 : 08:08:02

I need to insert into a field for testing, but the field has a contraint on an ID from abother field. I want to do un update with a random number between 2 defined numbers like 21 and 61??

Dave
Helixpoint Web Development
http://www.helixpoint.com

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-02-07 : 08:38:58

Got it
Declare @maxRandomValue tinyint = 15 , @minRandomValue tinyint = 29;

UPDATE tblTrkRecord
SET SubStatusId =
(SELECT CAST((@maxRandomValue + 1 - @minRandomValue) * RAND() + @minRandomValue AS tinyint) AS Expr1)
WHERE (StatusId = 7)

Dave
Helixpoint Web Development
http://www.helixpoint.com

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-02-07 : 12:32:40

You might also look into using the NEWID() function for generating random values or selecting random rows as RAND() is not very reliable in multi-row scenarios.

Populating a SQL Server Test Database with Random Data:
http://www.mssqltips.com/sqlservertip/2181/populating-a-sql-server-test-database-with-random-data/

Retrieving Random Rows from Table Using NEWID():
http://blog.sqlauthority.com/2012/11/16/sql-server-retrieving-random-rows-from-table-using-newid/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-02-08 : 07:35:14

You may also be interested to read this too http://beyondrelational.com/modules/2/blogs/70/posts/10831/populating-sample-data.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources