Visual FoxPro change account numbers in mass

I'm trying to renumber a host of account numbers where the apt_no field has some form of the word duplicate within it. This is Visual FoxPro and right now, I'm working in the command window.

The first line of the query below renumbers ALL of the account numbers. Lines 2 to 4 generates a list of account numbers that need to be renumbered. However, when I put them together, I get "Command contains unrecognized phrase/keyword. Can anyone help with this?

REPLACE ALL property.account_no WITH "999" + SUBSTR(ALLTRIM(property.account_no),4) ;
    WHERE (SELECT account_no FROM property ;
    WHERE account_no IN ( ;
        SELECT account_no FROM property WHERE apt_no LIKE '%Dup%' ))

I've tried the query in what I believe is the simplest form:

REPLACE ALL property.account_no WITH "999" + SUBSTR(ALLTRIM(property.account_no),4) WHERE account_no LIKE '%Dup%'

I've substituted the word WHILE and FOR for the WHERE keyword, but these didn't work either.

I have very little experience with VFP and I've inherited a large VFP project (original author, my father-in-law, is still available to me).

Solution

Since you don't have some sample data, this is what I understood as a sample data:

Create Cursor property (Id i, account_no c(20), apt_no c(50))
Insert Into property (Id, account_no, apt_no) Values (1, 'P001','')
Insert Into property (Id, account_no, apt_no) Values (2, 'P002','')
Insert Into property (Id, account_no, apt_no) Values (3, 'P003','')
Insert Into property (Id, account_no, apt_no) Values (4, 'P003','this is duplicate')
Insert Into property (Id, account_no, apt_no) Values (5, 'P003','Duplicated record')
Insert Into property (Id, account_no, apt_no) Values (6, 'P004','')
Insert Into property (Id, account_no, apt_no) Values (7, 'P004','Another duplicated row')

Browse Title 'Before Update'

* With REPLACE - xBase
Replace account_no With "999" + Substr(Alltrim(account_no),4) ;
    FOR Atc('duplicate',apt_no) > 0

* With SQL-Update
*!* UPDATE Property ;
*!*     SET Account_no = "999" + SUBSTR(ALLTRIM(account_no),4) ;
*!*     WHERE LOWER(apt_no) like '%duplicate%'

Browse Title 'After Update'

PS: It is perfectly valid to use Property.Account_no as you did and it is more clear IMHO, but generally VFP coders do not use it when it is clear what it is (we are lazy). Also note that I used two different checking for 'duplicate' and there are more. That is only because there are multiple ways to check if a string contains another (in VFP data is not case insensitive thus we need lower(apt_no) to check with LIKE. ATC() function is the case insensitive version of AT() function that you may read in help - checking the position of 'duplicate' in the string. If not found it is 0).

PS2: In xBase version (REPLACE) you cannot use LIKE that but there is a LIKE() function that you could use. I prefer ATC() for simple cases like this.