My database, which lives in MS Access and is accessible to DBeaver by the UCanAccess driver, contains two tables, which I’ll call Table1 and Table2. I want to update Table1 based on entries in Table2 and I have tried many variations on what I have found in StackOverflow, including:

UPDATE [Table1]
SET [Table1].[Column1] = [Table2].[ThingsToUpdateTo]
WHERE [Table1].[Column2] = [Table2].[Reference];

This gives an error: ” SQL Error [42501]: UCAExc:::5.0.1 user lacks privilege or object not found: [Table2].[ThingsToUpdateTo] “

I’ve tried using a SELECT statement in the SET line only, which gives the same error but on the next line, and I’ve tried using SELECT statements in both SET and WHERE. I have also tried putting [Table1] and [Table2] in the UPDATE line.

I feel like there must be something very simple that I’m missing that’s causing my statement not to be able to reference Table2 to update Table1. Thanks in advance!

I’ve tried using a SELECT statement in the SET line only, which gives the same error but on the next line, and I’ve tried using SELECT statements in both SET and WHERE. I have also tried putting [Table1] and [Table2] in the UPDATE line. These resolve the “user lacks privilege” type error and throws a “cardinality violation” error.

Tried

UPDATE Table1 INNER JOIN Table2
ON Table1.Column2 = Table2.Reference 
SET Table1.Column1 = Table2.ThingsToUpdateTo;

This did attempt to update records, but gave error: “unexpected token: INNER required: SET”

Tried

UPDATE Table1
SET Column1 = Table2.ThingSToUpdateTo
FROM Table1 JOIN Table2 ON t1.Column2 = t2.Reference;

Also attempted to update records, and gave error: “user lacks privilege or object not found: t2.ThingsToUpdateTo”.

I’ve also Tried running all of these queries in Access instead of in DBeaver, to no avail.