Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
[4.32] Rand() not working in queries |
Wed, Jun 13 2012 10:53 PM | Permanent Link |
Alessandra Romano | [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. |
Wed, Jun 13 2012 11:53 PM | Permanent Link |
Raul Team Elevate | 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. |
Thu, Jun 14 2012 7:27 AM | Permanent Link |
John Hay | 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 |
Thu, Jun 14 2012 4:17 PM | Permanent Link |
Raul Team Elevate | 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 |
Fri, Jun 15 2012 9:01 AM | Permanent Link |
Alessandra Romano | > 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. |
Fri, Jun 15 2012 9:01 AM | Permanent Link |
Alessandra Romano | > update mytable set counter=counter+if(rand(10)>5 then 1 else 0)
Great! Thanks!!! A. |
Sat, Jun 16 2012 10:49 AM | Permanent Link |
Raul Team Elevate | 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. > |
This web page was last updated on Wednesday, June 12, 2024 at 01:54 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |