How to insert an INSERT using SELECT into a table and query

Question

I have a problem with access to UPDATE based on JOIN.

There are 2 instructions that I need to combine:

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 request but works fine

 UPDATE tableA a SET a.f1 = 'x' 

works great.

Now I am trying:

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

Failed, Access says:

The operation must use an updated query.

This is stupid because there is no field in sight. I tried several other solutions using FROM, JOIN Subselect, but did not find a solution.

Does anyone know a smart solution?

Answer 1

Try the syntax update .. join :

 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 join syntax rather than where syntax to join 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 updated. For example, if viewB has GROUP BY or UNION, it will not be updated.

---

EDIT: if viewB 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)) 

Answer 3

you need to provide permissions. remove only read from this folder or mdb file so that it can update this link.

The operation must use an updated query. (Microsoft JET Database Engine)

ASp.net forum topic for: opreation should use updateble request

Answer 4

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

Answer 5

Sometimes, when Access says that you are not creating an updatable request, it responds that the property has unique records for the request "yes."