UCanAccess Update Query with a Left Join Question

[Luke]

Hi guys,

I'm strugglnig to get my Update query working.

The following works as a standard Access Update Query:

UPDATE DocumentItems LEFT JOIN DocumentHeaders ON DocumentItems.DocID = DocumentHeaders.ID
SET DocumentItems.CustomNumber05 = DocumentItems.CustomNumber05 + 1
WHERE (((DocumentHeaders.DocNo)="NNCO1031") AND ((DocumentItems.ManufacturerPartNumber)="test") AND ((DocumentItems.Description)="test123"));

and translated into Java (Netbeans IDE) with variables:

String strSQLString = "UPDATE DocumentItems LEFT JOIN DocumentHeaders ON DocumentItems.DocID = DocumentHeaders.ID \n"
+ "SET DocumentItems.CustomNumber05 = DocumentItems.CustomNumber05 + ? \n"
+ "WHERE (((DocumentHeaders.DocNo)= ?) AND ((DocumentItems.ManufacturerPartNumber)= ?) AND ((DocumentItems.Description)= ?));";

The error I receive is:

net.ucanaccess.jdbc.UcanaccessSQLException: unexpected token: LEFT required: SET

Does this mean currently UCanAccess does not support update queries that contain a Left Join?

Let me know if you require anymore parts of my code.

Many thanks

Update 1: I am using the latest release version of UCanAccess, I have also tried the query as stated above from Access, in the console.bat which returned the same error.

[Marco Amadei]

Your SQL isn't standard and just few agile DBMS do support something like that but not the UCanAccess underlying engine hsqldb.
Also, the of a LEFT OUTER JOIN (which would be supported) isn't logically needed because of the DocumentHeaders.DocNo="NNCO1031" condition, and so you may use a INNER JOIN instead,

YET

you can re-code your SQL using something interesting (and agile as well) in the SQL 2003 standard:

MERGE INTO DocumentItems USING
(SELECT * FROM DocumentHeaders WHERE DocumentHeaders.DocNo="NNCO1031") AS C
ON DocumentItems.DocID = C.ID AND DocumentItems.ManufacturerPartNumber="test" AND DocumentItems.Description="test123"
WHEN MATCHED THEN UPDATE SET DocumentItems.CustomNumber05 = DocumentItems.CustomNumber05 + 1

Please let me know your findings.
Cheers Marco
P.S. You may try it with the console which in turn uses UCanAccess.

[Luke]

Hi Marco,

Thanks for your reply.

I have tried to adapt the code in Netbeans:

String strSQLString = "MERGE INTO DocumentItems USING \n"
+ "(SELECT * FROM DocumentHeaders WHERE DocumentHeaders.DocNo= ?) AS C \n"
+ "ON DocumentItems.DocID = C.ID AND DocumentItems.ManufacturerPartNumber= ? AND DocumentItems.Description= ? \n"
+ "WHEN MATCHED THEN UPDATE SET DocumentItems.CustomNumber05 = DocumentItems.CustomNumber05 + ?;";

However I get the following error (and cannot see the reasons why):

net.ucanaccess.jdbc.UcanaccessSQLException: data exception: invalid character value for cast

I tried the code in the .bat file andit returns no errors, however it does return

No rows affected

Which should not be the case; 1 row should be affected. Not sure if I'm doing something wrong?

Many thanks,

spencercnuk

[Marco Amadei]

I tried this code on a supposed database before replying, and it worked fine.
May you send/attach
-part of your code(just the part in which you create the PreparedStament and call set methods on it)
-a copy of your database ?

If you want you can use my private email amadei.mar@gmail.com, but please, should the db have private or personal data, clean it using exemplification data (it's useful just to reproduce the issue).
Cheers Marco

[Luke]

Hi Marco,

Your email help was fantastic thank you very much!