Access: Update Table from a joint on the same table and a secondary table

Question

I am trying to update a table using records from the same table and another table but MS ACCESS says my SQL is wrong. I can't understand where the error is, to me it looks ok:

UPDATE Clients AS T1

 INNER JOIN

(SELECT DISTINCT 
Clients.ID_Client AS FED, 
Clients.Name, 
SecondTable.Client_Name, 
SecondTable.ABI_Code AS ABI

FROM  SecondTable INNER JOIN Clients ON SecondTable.FieldToUpdate=Clients.Name) AS T2

SET T1.FieldToUpdate = T2.FED
WHERE T1.ABI_Code =T2.ABI

It says "Syntax Error" in the Update query and the cursor is on the SET instruction-

score 0 · Answer 1 · answered Dec 18 '14 at 10:36

0

Try following query:

    UPDATE T1 
        SET T1.FieldToUpdate = T2.FED
    FROM 
        Clients AS T1
    INNER JOIN
        (SELECT DISTINCT 
        Clients.ID_Client AS FED, 
        Clients.Name, 
        SecondTable.Client_Name, 
        SecondTable.ABI_Code AS ABI
        FROM  SecondTable INNER JOIN Clients ON SecondTable.FieldToUpdate=Clients.Name) AS T2
    ON
        T1.ClientID = T2.ClientID
    WHERE 
        T1.ABI_Code =T2.ABI

answered Dec 18 '14 at 10:36

Sandeep

1,182
3
11
26

Since I use an alias for ClientID on T2 I suppose should use: ON T1.ClientID = T2.FED but when I run the query now I have an error that translated is something like: "For the operation is necessary a query that can be updated" (!) – nico9T Dec 18 '14 at 10:41
@NicolaPrada can you try removing where and add it to join itself ON T1.ClientID = T2.FED AND T1.ABI_Code =T2.ABI. – Sandeep Dec 18 '14 at 10:54
@NicolaPrada can you provide exact error message which you are receiving – Sandeep Dec 18 '14 at 11:00
error 3073: "Operation must use an updatable query.". I guess this explain the problem: http://stackoverflow.com/questions/170578/operation-must-use-an-updatable-query-error-3073-microsoft-access – nico9T Dec 18 '14 at 11:06
@NicolaPrada what is type of FieldToUpdate in clients and SecondTable and is it primary key? – Sandeep Dec 18 '14 at 11:13
type is text and yes, Clients_ID is a primary key – nico9T Dec 18 '14 at 11:25
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/67216/discussion-between-sandeep-and-nicola-prada). – Sandeep Dec 18 '14 at 11:28
I solved using a temporary table. The problem is the Access JET Engine. Thanks. – nico9T Dec 18 '14 at 11:52

Access: Update Table from a joint on the same table and a secondary table

1 Answers1