Update SQL works incorrectly with RAND() function in WHERE clause

View products that this article applies to.

This article was previously published under Q139048

SYMPTOMS

Using the RAND() in an SQL UPDATE's WHERE clause can cause random results to occur.

Back to the top

The value of RAND() changes each time it is evaluated, unlike most FoxPro functions. FoxPro's internal SQL parser evaluates it once to do the Rushmore optimization and once more as each filter condition is tested for each record. This causes unpredictable, random results.

Back to the top

RESOLUTION

Instead of using the RAND() function in the WHERE clause, assign the value of the RAND() function to a memory variable. Then use the variable in the WHERE clause. For an example, please see the "Code to Correct Problem" section of this article.

A new RAND() can be generated for every record tested in the filter by putting RAND() in a User Defined Function (UDF) and calling the UDF in the SQL command in place of RAND():

   SELECT * FROM <table> WHERE RAND() > .5

In this example the article's CAUSE section applies:

   SELECT * FROM <table> WHERE myudf() > .5
   PROCEDURE myudf()
   RETURN RAND()

Back to the top

MORE INFORMATION

Code to reproduce the problem

The following code illustrates the problem. If there wasn't a problem, the code should make 1000 updates to the table. Instead, the update fails either 999 or 1000 times.

   *-- Code Begins Here
   CREATE TABLE testfile.dbf ;
        (mach_id i, mach_name c(10), db_key i, updcount i)
   INDEX ON db_key TAG db_key
   SET ORDER TO
   SET STATUS BAR ON

   FOR ii = 1 TO 1000
        INSERT INTO testfile.dbf VALUES (0, "", ii, 0)
   ENDFOR

   USE IN testfile
   USE testfile.dbf IN 0 SHARED
   SELECT testfile
   failcount = 0
   passcount = 0

   FOR ii = 1 to 1000
        *updval = INT(RAND() * 1000 + 1)
        UPDATE testfile ;
             SET mach_id = 1, ;
             mach_name = 'JOHNDOE', ;
             updcount = updcount+1 ;
             WHERE db_key = INT(RAND() * 1000 + 1)
        IF _TALLY = 0
             Failcount=Failcount + 1
        ELSE
             passcount=passcount + 1
        ENDIF
        SET MESSAGE TO ALLTRIM(STR(FAILCOUNT))+' Failed, ';
             +ALLTRIM(STR(PASSCOUNT))+' Passed'
   ENDFOR
   *-- Code Ends Here

Back to the top

Code to correct the problem

To fix the problem, remove the '*' from the updval line and substitute 'updval'for 'INT(RAND() * 1000 + 1)' of the UPDATE's WHERE clause. The new line would look like the following: