Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 6 of 6 total |
Update with a join |
Mon, Aug 18 2008 3:40 AM | Permanent Link |
Kevin Kozlowski | 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 |
Mon, Aug 18 2008 5:00 AM | Permanent Link |
Fernando Dias Team Elevate | Kevin,
What version of DBISAM are you using? -- Fernando Dias [Team Elevate] |
Mon, Aug 18 2008 9:17 AM | Permanent Link |
Kevin Kozlowski | 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 |
Mon, Aug 18 2008 10:35 AM | Permanent Link |
"Robert" | "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 |
Mon, Aug 18 2008 11:53 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Aug 18 2008 1:15 PM | Permanent Link |
Kevin Kozlowski | 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 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |