UPDATE SQL with subquery

Forum for DBF Commander discussing.

UPDATE SQL with subquery

Postby somethingsimil » Wed Dec 31, 2014 7:28 pm

I am not sure if I'm hitting a DBF Commander limitation or a FoxPro limitation, but the following query...

Code: Select all
update "C:\Users\UserOne\Desktop\TRANSACT.DBF" t
where t.csr=''
set t.csr = (select substr(i.c, 1, 2)
             from "C:\Users\UserOne\Desktop\INS.DBF" i
            where substr(t.pol_idx, 1, 7) = i.rec
              and i.key = 'C')


...returns the following error...

" -> File 'transact.dbf t' does not exist "
somethingsimil
 
Posts: 30
Joined: Tue Jul 08, 2014 6:05 pm

Re: UPDATE SQL with subquery

Postby somethingsimil » Wed Dec 31, 2014 11:03 pm

I should clarify that I'm trying to do a variable update by using a sub-query. I have only done this before on Oracle, Sybase, and MySQL, so I'm not sure if it's possible in FoxPro at all. It seems to not like the alias for a table name that's being updated.
somethingsimil
 
Posts: 30
Joined: Tue Jul 08, 2014 6:05 pm

Re: UPDATE SQL with subquery

Postby Admin » Thu Jan 01, 2015 11:08 am

VFP syntax doesn't allow using of local aliases in UPDATE clause. But you can try the following:

Code: Select all
update "C:\Users\UserOne\Desktop\TRANSACT.DBF"
where transact.csr=''
set transact.csr = (select substr(ins.c, 1, 2)
from "C:\Users\UserOne\Desktop\INS.DBF" 
where substr(transact.pol_idx, 1, 7) = ins.rec
and ins.key = 'C')
Best regards,
Oleg Zhechkov
User avatar
Admin
Site Admin
 
Posts: 124
Joined: Wed Apr 21, 2010 7:27 pm


Return to DBF Commander Professional

Who is online

Users browsing this forum: No registered users and 10 guests

cron