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?

Answer 1

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?