🚀 go-pugleaf

On Wed, 02 Sep 1998 12:04:03 -0400, Bob Knapp <nectel@erols.com> wrote:

>I am trying to perform the UPDATE function with SQL in dbase 7.01. What
>I would like to do is replace all values in a field when a match is
>made.
>
>My understanding is that the update command works as follows  UPDATE
><table name> SET <colum name> = <expression> WHERE <search condition>.
>
>This command works fine from dbase :
>
>UPDATE  DATA1.DBF  SET VALUE1 = '1000'  WHERE  VALUE2 = 'TEST'
>
>All VALUE1 occurances are changed to 1000 when VALUE2 is equal to TEST.
>
>The problem is this: In the real world '1000' and 'TEST' are being sent
>in as variables and changed all the time.
>
>If I set these values to variables it won't work .  ie. TEMP1 = '1000'
>,  TEMP2 = 'TEST'
>
>        UPDATE DATA1.DBF SET VALUE1=TEMP1 WHERE VALUE2=TEMP2
>
>
>I  even tried to use &TEMP1 and &TEMP2 as well as '&TEMP1' and '&TEMP2'.
>
>Nothing seems to work and there is little or no information on how to
>use SQL with a variable replacement.
>
>What is the secret syntax, or does visual dbase not support common SQL
>language.

In common SQL language, you would use parameters in the SQL statement to
represent values that will not be known or assigned until just before the
SQL statement is executed. Parameters are identified by a colon at the
beginning of the parameter name. For example, in the statement below the
token LastNameParam is a parameter.

  UPDATE "Addresses.dbf" A
  SET A.LastName = :LastNameParam

Local SQL (a subset of SQL-92) supports this syntax convention. However, a
little sleight-of-hand is apparently done on the dBASE side to extend this
functionality. In dBASE, if you name the parameter the same as a dBASE
memory variable, the memory variable's value is automatically used as the
value of the parameter. (Ordinarily you would need to explicitly assign the
value of the variable to the parameter.)

So you should just be able to prepend your variable names (now already in
the SQL statement) with colons to cause them to also be treated as query
parameters.

  UPDATE "DATA1.DBF" D
  SET D.VALUE1 = :TEMP1
  WHERE (D.VALUE2 = :TEMP2)

This is, of course, assuming you have two memory variables named TEMP1 and
TEMP2.

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "The knowledge of the world is only to
Technical Publications              be acquired in the world, and not in a
INPRISE Corporation                 closet."
http://www.inprise.com/delphi          -- Earl of Chesterfield (1694-1773)