SQL Server Performance

Random Value update

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by w84me2say, Jul 17, 2007.

  1. w84me2say New Member

    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
  2. dineshasanka Moderator

    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/
  3. upstart New Member


    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]
  4. khtan New Member

    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
  5. khtan New Member

  6. w84me2say New Member

    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

Share This Page