SQL Server Performance Forum – Threads Archive

Random Value update

I have a tables with a field of values and need to update it randomly between 1 or 5% negative or positive I believe I know the step, not sure where to begin –
select values from table Value +/- Random n% between 1 and 5
14654654 + n% = new# Update values w/ new# Repeat with next value

---

I am not too sure about the requirment.
But you can generate random -5 to 5 from
select cast((rand() * 10) as int) – 5 —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

---

You might have to loop through your records, but if your doing a simple update then you could try something like this: UPDATE customer
set total_spent = total_spent + [however you do the random number generation]
where customer_id = [whatever criteria you need to sort by]

---

try this
CREATE TABLE #temp
(
valuedecimal(10,2)
) INSERT INTO #temp (value)
SELECT10UNION ALL
SELECT20UNION ALL
SELECT30UNION ALL
SELECT40UNION ALL
SELECT50 UPDATE#temp
SETvalue= value + (value * (CONVERT(int, CONVERT(varbinary, NEWID())) % 5) / 100.0) SELECT*
FROM#temp DROP TABLE #temp KH

---

Or you can make use of a random generation function by MVJ from herehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69499&SearchTerms=F_RANDOM_INTEGER
KH

---

Thanks for all your advice, although it maybe a little more complicated than I need for this project. I have a ‘valeudetails’ table, with a field called ‘avmvalue’ that contains a rounded off number and a field called ‘AVM’ that is blank. I need to take the ‘avmvalue’ field and either + or – between 1-5 percent randomly. I then need to insert that value into the ‘AVM’ column. The one below works except it just +/- the actual number and not the by percentage. Thanks in advance for all your help Susan update valuedetails set AVM = avmvalue, AVMValue + case when RAND(CAST(NEWID() AS VARBINARY)) > 0.50 then CAST(RAND(CAST(NEWID() AS VARBINARY))*-5+1 AS INT) else CAST(RAND(CAST(NEWID() AS VARBINARY))*5+1 AS INT) end from valuedetails

---

]]>