UPDATE from one table to another doesn't work

[David Naegle]

I am writing an Access database update program in Java using UCANACCESS 3.0.2. I can access the .mdb file and read and build tables, but I am having a problem with an UPDATE statement that will not work. I've tried many variations, all for naught.
I have two tables in a single microsoft .mdb file. I am trying to update a column in one table from the other table where the key column values match between the two table. It seems simple enough. In this example sql string col2 is the key column in both tables. Table1 has a superset of the rows in table2.

When I run this I get object not found table2.col1 with the names in all upper case.

UPDATE table2, table1
SET table2.col1 = table1.col1,
FROM table1, table2
WHERE table1.col2 = table2.col2

If I run this syntax directly then I get and error because UCANACCESS won't accept the FROM. So I generated the SQL UPDATE in MS Access directly.

Here is the SQL that works in MS Access but generates errors when run through the UCANACCESS driver.
My Java program created and populated both tables so I am pretty sure it is not a permissions issue.
[combo] is the table to be updated, [ORCADPartInfo_QA] is the table with the source data.
I've tried this with ! and periods, same error either way.
Also, listing both table names after the UPDATE generates an error in UCANACCESS:
UCAExc:::3.0.2 unexpected token: , required: SET
So I removed the second table in my java program.

UPDATE combo, ORCADPartInfo_QA
SET combo.Attachment_url = [ORCADPartInfo_QA]![attachment_url]
WHERE (([combo]![SanDisk_PN]=[ORCADPartInfo_QA]![part_number]));

This is the exception:
UCAExc:::3.0.2 user lacks privilege or object not found: ORCADPARTINFO_QA.PART_NUMBER

Any ideas?
Thanks

[Gord Thompson]

The syntax that Access SQL uses for UPDATE operations with JOINs is rather unusual, and it appears that HSQLDB (the backing database for UCanAccess) does not like it. I just tried the following ANSI SQL syntax (ref: here) and it seemed to work fine for me:

sql =
    "UPDATE combo SET " +
        "Attachment_url = (" +
            "SELECT attachment_url FROM ORCADPartInfo_QA " +
            "WHERE [combo].[SanDisk_PN]=[ORCADPartInfo_QA].[part_number] " +
        ") " +
    "WHERE EXISTS ( " +
        "SELECT * FROM ORCADPartInfo_QA " +
        "WHERE [combo].[SanDisk_PN]=[ORCADPartInfo_QA].[part_number] " +
    ") " +
    "";

[David Naegle]

Excellent, thank you Gord for your fast, accurate response. This works like a champ!
It looks like I better get more familiar with ANSI SQL. Thanks so much!

[media1mogul]

Gord,
Thank you, thank you! THANK YOU!
I am brand new to DBEAVER and using UCANACCESS to access an MS Access acount.
I had this EXACT issue and it's had me close to tears.