Update all records with random value ?

[alvaro_xav]

This is my first post. Please help me.

Here's my DB :

Status : Number (Long Integer)
Random : Number (Double)

If Random value is around 0-0.9, the status value is 1
If Random value is around 0.9-1, the status value is 0

Here's my Query :

UPDATE DB Set Random = RND()

Rnd() give random value, but each records have the same value,
let's say 0.71212154

How can I update all records with random value,
with a single query ?

 

[dsigner]

I think that what you want is to select 10% of the records at random for some sort of test or processing. The easiest way is to use an autonumber field which you can set to random. This will attach a random number when the record is created. You can then use your query to select a different 10 % of the range each time. Getting sophisticated the query could select 10 different 1% ranges which would provide assurance the you got different test sets.

There are drawbacks. You may need to refresh the random numbers every so often to avoid duplicating sets over a period of time. Also you can only have one autonumber per table. If you have already used that then you will need a separate table with a one-to-one link to hold the random autonumber.

 

[alvaro_xav]

Thanks for reply, but I really want to update all records
with random value, RND() function will duplicate
the random value to all records. The help say something about 'Randomize'
but I cannot use it in query. I'm using Delphi and ADO to access "MS Access" Database. Randomize in my program doesn't help RND() to display random value for each records.

BTW, if I use auto number, I cannot choose the range of the random number generated, isn't ?

 

[dsigner]

Ok, I don't think you will do it with a query but I admit to not having tried that hard. Could you generate a temporary linked table with just the primary key and a random autonumber then you could use that in the query.
The answer to the range question is that most pseudo random generators that I have met merely provide 0 - 1. You then multiply by the range that you want. The precision is usually way above what you need so it is not a problem. You could certainly do that here.

 

[alvaro_xav]

Thanks for supporting me. I've found the answer "yesterday".
Basically, it's simple, I just need a field with auto-generated number.
Here's the field :

Auto : Number (Long Integer)
Random : Double

And just call RND(Auto) - this will generate random value for all records.
UPDATE TABLE_NAME SET Random=RND(Auto)

It works

And, I've 'useful' additional info for you :
If the parameter for RND is negative, it'll result in same number.
Useful in my case.