🚀 go-pugleaf

Dear Steve,
    Thank you for your attempt in solving this problem. The result of your
suggestion produced the following error:
Error: unallowed phrase/keyword in command D.VALUE1=:TEMP1

The method submitted by Romain Strieff did work.

Thanks Again,
Bob Knapp

Steve Koterski wrote:

> 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)