Update DATA between two tables

asked 2015-04-01 07:01:58 +0200

MQ-818 gravatar image

So I have two tables; tblAppDetail and tblMainCustInfo. I need to update one column (Appt_Sidemark) between these two tables. tblApptDetail has field called: Appt_Sidemark and tblMainCustInfo has field called: Sidemark. These two tables has CustID Linked. When I try the following SQL, I am getting

1: Unexpected token:   in statement [update "tblApptDetail" set "tblApptDetail"."Appt_Sidemark" =]

Here is what I am running at TOOLS-->SQL update "tblApptDetail" set "tblApptDetail"."Appt_Sidemark" =  (select "tblMainCustInfo"."Sidemark" from "tblMainCustInfo" where "tblApptDetail"."CustID" = "tblMainCustInfo"."CustID") where exists (select "tblMainCustInfo"."Sidemark" from "tblMainCustInfo" where "tblApptDetail"."CustID" = "tblMainCustInfo"."CustID")

I know it is me....just not smart enough to figure it out by myself. So your kind help is greatly appreciated.

edit retag flag offensive close merge delete


The problem looks like it's the WHERE EXISTS part. Looks like new HSQLDB supports IF EXISTS. Not sure if supported in 1.8. If that is problem, not helpful error message. Syntax like that is problematic in other database systems when using subqueries, even when it seems like it should work.

doug gravatar imagedoug ( 2015-04-01 14:32:15 +0200 )edit

Where do I check my HSQLDB version. I am running Windows 8.1 Pro / 64bit LibreOffice Base Version: Build ID: 45e2de17089c24a1fa810c8f975a7171ba4cd432 Locale: en_US

I changed it to IF EXISTS and gave me error saying if in IF statement....So, I changed it back to WHERE EXISTS and took a change and WORKED .... !!! I tried it three different times and every time it worked just the way original code was. Not sure why it didn't run the other day; when I posted the message...


MQ-818 gravatar imageMQ-818 ( 2015-04-04 04:00:36 +0200 )edit

You can use the macro at this link, modifying the string 'hsqldb' to the name of an actual LibreOffice Base filename on your system. For me on LibreOffice it reported HSQLDB product version: 1.8.0.

doug gravatar imagedoug ( 2015-04-04 05:30:07 +0200 )edit