SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select random between 2 numbers
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

helixpoint
Constraint Violating Yak Guru

279 Posts

Posted - 02/07/2013 :  08:08:02  Show Profile  Reply with Quote
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

279 Posts

Posted - 02/07/2013 :  08:38:58  Show Profile  Reply with Quote
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
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/07/2013 :  12:32:40  Show Profile  Reply with Quote
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/
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 02/08/2013 :  07:35:14  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000