[using 4.32 + D2010]

I'd like to increment some random records in my dataset, but with the
following statement:

update mytable set counter=counter+1 where rand(10)>5;

*all* of the records are affected.
The only way to perform the random increment is to add a column to the
table (via alter table), set the column with the random number, do an
update based on the value of that column, and eventually removing the
columns.

Another weird *and* *non* working thing is:

update planday set checkin=starttime+rand(60)*60*60*1000

where checkin and starttime are both timestamp fields.
I'd like to set the checkin time = the starttime + some random minutes.
The query is not run claiming a 11949 parsing error "found * in update
sql instead of a null, integer, etc. value"

A.

Looks fine to me - the where clause is computed once and then used to
select a subset of the records for the update - hence in your case
either all records are selected or none are depending on rand result.

To achieve a random counter increment on a per row basis you should do
something like this :

update mytable set counter=counter+rand(1);

This would add either 0 or 1 to each record counter resulting in random
counter update.

for the 2nd one make sure you cast the rand as integer so something like
this

update planday set starttime = (checkin + (cast(rand(60) as integer) *
3600000))


Raul


On 6/13/2012 10:53 PM, Alessandra R. wrote:
> [using 4.32 + D2010]
>
> I'd like to increment some random records in my dataset, but with the
> following statement:
>
> update mytable set counter=counter+1 where rand(10)>5;
>
> *all* of the records are affected.
> The only way to perform the random increment is to add a column to the
> table (via alter table), set the column with the random number, do an
> update based on the value of that column, and eventually removing the
> columns.
>
> Another weird *and* *non* working thing is:
>
> update planday set checkin=starttime+rand(60)*60*60*1000
>
> where checkin and starttime are both timestamp fields.
> I'd like to set the checkin time = the starttime + some random minutes.
> The query is not run claiming a 11949 parsing error "found * in update
> sql instead of a null, integer, etc. value"
>
> A.

Alessandra

>
> I'd like to increment some random records in my dataset, but with the
> following statement:
>
> update mytable set counter=counter+1 where rand(10)>5;
>

In addition to what Raul has said (I think it should be Rand(2) not Rand(1)), if you want to use your logic (possibly to
alter the probability of an increment) you could use

update mytable set counter=counter+if(rand(10)>5 then 1 else 0)

John

John

> In addition to what Raul has said (I think it should be Rand(2) not Rand(1)),

Thanks for the catch - wrote it late at night without dbsys to try it with.

Raul

> Looks fine to me - the where clause is computed once and then used to
> select a subset of the records for the update - hence in your case
> either all records are selected or none are depending on rand result.

thanks Raul, i didn't realize that the where clause was evaluated only
once, at first, obtaining the subset. I thought it was applied
dinamically to all of the records. You have a good point )

A.

> update mytable set counter=counter+if(rand(10)>5 then 1 else 0)

Great! Thanks!!!

A.

It's because there is no column reference in where clause hence from
point of dbisam engine it's a static clause and there is no need to
evaluate it once per row - after correct operation we want the DB engine
to be as fast as possible.

Raul


> thanks Raul, i didn't realize that the where clause was evaluated only
> once, at first, obtaining the subset. I thought it was applied
> dinamically to all of the records. You have a good point )
>
> A.
>