I have an update statement that needs to set a field to a value contained in another
table. The DBISAM manual has an example showing exactly what I need on page 187.

Here's the code I'm trying to use:

update Doctor D set D.TerritoryID=T.TerritoryID
from Doctor D left outer join Territory T
on T.State=D.State

------
Error
---------------------------------------------
An error has occurred in the current SQL statement and the error message is:
DBISAM Engine Error # 11949 SQL parsing error - Table correlation name D in JOIN clause
expression is invalid in UPDATE SQL statement at line 3, column 12
---------------------------------------------

The only difference between the above and the example on page 187 of the user's manual are
the table and field names. I've tried various combination of including and not including
table correlation names, but I've been unable to come up with a syntactically correct SQL
statement.

I must be overlooking something very simple as I'm sure DBISAM properly handles statements
like the above, but I can't seem to find where I've gone wrong.

Any assistance would be greatly appreciated.

Thanks,

-Kevin

Kevin,

What version of DBISAM are you using?

--
Fernando Dias
[Team Elevate]

Fernando Dias

>> What version of DBISAM are you using?

Oops, left of some of those important details. DBISAM C/S v4.25 Build 6 under CodeGear RAD
Studio 2007 (all patches). It is a native Win32 VCL app.

-Kevin

"Kevin Kozlowski" <kjkozAtcomcastDotnet> wrote in message
news:427A025A-A5A1-4553-8E66-D7FFBCB246CF@news.elevatesoft.com...
>I have an update statement that needs to set a field to a value contained
>in another
> table. The DBISAM manual has an example showing exactly what I need on
> page 187.
>
> Here's the code I'm trying to use:
>
> update Doctor D set D.TerritoryID=T.TerritoryID
> from Doctor D left outer join Territory T
> on T.State=D.State
>

Looks good to me. Are you sure you have a State field in the doctor table?

Robert

Kevin,

<< An error has occurred in the current SQL statement and the error message
is:
DBISAM Engine Error # 11949 SQL parsing error - Table correlation name D in
JOIN clause
expression is invalid in UPDATE SQL statement at line 3, column 12 >>

That statement works fine here with 4.26 when I run it with the two tables
with the two fields defined in each.  I don't think there's been any bug
fixes that would change this from 4.25, but it might not hurt to try it with
the 4.26 Database System Utility just to be sure.

--
Tim Young
Elevate Software
www.elevatesoft.com

Robert,


<<
Looks good to me. Are you sure you have a State field in the doctor table?
>>

That was it. The Territory file no longer contain a State field. The DB schema was
modified such that the State is now stored in a different table (TerritoryRegions). I knew
DBISAM was correct, and I was doing something wrong somewhere. Now I know where!!!

Thanks for the tip. I'd been looking at that all day, and never would have thought to
recheck the schema!

-Kevin