How to INSERT using SELECT on Table and Query

Question

I have a problem in Access to UPDATE based on a JOIN.

There are 2 Statement which I need to merge:

SELECT a.f1, a.f2, a.f3
  FROM tableA a, viewB b
  WHERE a.f2 = b.f2 AND a.f3 = b.f3

viewB is a query, but works fine

UPDATE tableA a
  SET a.f1 = 'x'

works fine, too.

Now I try:

UPDATE tableA a, viewB b
  SET a.f1 = 'x'
  WHERE a.f2 = b.f2 AND a.f3 = b.f3

Fails, Access says:

Operation must use an updateable query.

This is stupid, because there is no field touched in viewB. I tried several other solutions with FROM, JOIN Subselect, but did not found a solution.

Does anybody know a smart solution on that?

Answer 1

Try the update .. join syntax:

update tableA a
inner join viewB b on a.f2 = b.f2 and a.f3 = b.f3
set a.f1 = 'x'

Or alternatively:

update tableA a
set a.f1 = 'x'
where exists (select * from viewB b where a.f2 = b.f2 and a.f3 = b.f3)

Answer 2

Use the join syntax, not the where syntax in order to join the tables:

UPDATE tableA AS a 
INNER JOIN viewB AS b
    ON (a.f2 = b.f2) AND (a.f3 = b.f3)
SET a.f1 = 'x';

Also note that it will only work, if viewB is updatable. If viewB has a GROUP BY or a UNION, for instance, it will not be updatable.

---

EDIT: If viewB is not updatable a subquery instead of a join will help:

UPDATE tableA AS a
SET a.f1 = 'x'
WHERE EXISTS (SELECT *
              FROM viewB AS b
              WHERE (a.f2=b.f2) AND (a.f3=b.f3))