How can I use 'update where' select in FoxPro?

Question

I am totally new to FoxPro (and quite fluent with MySQL).

I am trying to execute this query in FoxPro:

update expertcorr_memoinv.dbf set 'Memo' = (select 'Memo' from expertcorr_memoinv.dbf WHERE Keymemo='10045223')  WHERE Keydoc like "UBOA"

I got the error:

function name is missing )

How can I fix it?

Can you provide a few sample data, and an expected result? – Stefan Wuebbe Jun 24 '22 at 06:09 — Stefan Wuebbe, Jun 24 '22 at 06:09

score 4 · Accepted Answer · edited Aug 12 '22 at 12:59

In FoxPro SQL statements you would not 'single-quote' column names. In Visual FoxPro version 9 the following sequence would run without errors:

CREATE TABLE expertcorr_memoinv (keydoc Char(20), keymemo M,  Memo M)

Update expertcorr_memoinv.dbf set Memo = (select Memo from expertcorr_memoinv.dbf WHERE Keymemo='10045223')  WHERE Keydoc like "UBOA"

If you would provide a few sample data and an expected result, we could see whether the line you posted would do what you want after correcting the single-quoted 'Memo' names.

NB 1: "Memo" is a reserved word in FoxPro.

NB 2: As you know, the ";" semicolon is a line-continuation in Visual FoxPro, so that a longer SQL statement can be full; of; those;

So that the Update one-liner could be written as:

Update expertcorr_memoinv ;
    Set Memo = (Select Memo From expertcorr_memoinv ;
        WHERE Keymemo='10045223') ;
    WHERE Keydoc Like "UBOA"

NB 3: Alternatively, you can SQL Update .... From... in Visual FoxPro, similar to the Microsoft SQL Server feature. See How do I UPDATE from a SELECT in SQL Server?

Sure, and it works in most cases, but it's still a reserved word in the `foxpro` programming language — Stefan Wuebbe, Jun 24 '22 at 06:51

Cetin Basoz · Answer 2 · 2022-08-12T13:14:59.737

I would do that just as Stefan showed. In VFP, you also have a chance to use non-SQL statements which make it easier to express yourself. From your code it feels like KeyMemo is a unique field:

* Get the Memo value into an array
* where KeyMemo = '10045223'
* or use that as a variable also
local lcKey
lcKey = '10045223'
Select Memo From expertcorr_memoinv ;
     WHERE Keymemo=m.lcKey ;
     into array laMemo

* Update with that value
Update expertcorr_memoinv ;
    Set Memo = laMemo[1] ;
    WHERE Keydoc Like "UBOA"

This is only for divide & conquer strategy that one may find easier to follow. Other than that writing it with a single SQL is just fine.

PS: In VFP you don't use backticks at all. Single quotes, double quotes and opening closing square brackets are not used as identifiers but all those three are used for string literals.

'This is a string literal'
"This is a string literal"
[This is a string literal] 

"My name is John O'hara"
'We need 3.5" disk'
[Put 3.5" disk into John's computer]

There are subtle differences between them, which I think is an advanced topic and that you may never need to know.

Also [] is used for array indexer. Any one of them could also be used for things like table name, alias name, file name ... (name expression) - still they are string literals, parentheses make it a name expression. ie:

select * from ('MyTable') ...

copy to ("c:\my folder\my file.txt") type delimited

How can I use 'update where' select in FoxPro?

2 Answers2