Access - query update with Inner Join

Question

I have this query:

UPDATE client
SET client.[client_history] = 10
FROM  [T_CLIENT] AS client
     INNER JOIN (SELECT [client_id], SUM([final_price])
            FROM [T_PURCHASE]
            GROUP BY [client_id]) AS p
     ON client.[client_id] = p.[client_id]

When i execute this query on access, i get "Syntax Error". Did you see something wrong?

Thank you

You're not using the subquery that gets joined at all. If you share a more realistic attempt, I could show you how to replace a joined subquery with a domain aggregate — Erik A, Apr 29 '18 at 21:06
Eh... Explain to me what that joined subquery is doing, then. You're not using any of it's fields, why is it there? — Erik A, Apr 29 '18 at 21:44
I wanna join all list of purchases of one client, after that, i wanna save the total amount in table client column, field client_history. — Juan.Queiroz, Apr 29 '18 at 21:50

score 2 · Answer 1 · answered Apr 29 '18 at 20:37

2

Does the syntax work without FROM:

UPDATE [T_CLIENT] AS client INNER JOIN
       (SELECT [client_id], SUM([final_price])
        FROM [T_PURCHASE]
        GROUP BY [client_id]
       ) AS p
      ON client.[client_id] = p.[client_id]
    SET client.[client_history] = 10;

answered Apr 29 '18 at 20:37

Gordon Linoff

1,242,037
58
646
786

Yes, and probably he want something like `SUM([final_price]) AS the_sum` and then `SET client.[client_history] = the_sum;`, i.e. the alias is required to be able the refer to the sum. Relying on column names given automatically by Access is dangerous, because they can differ in different language versions of Access. – Olivier Jacot-Descombes Apr 29 '18 at 20:50
That won't work, since the query won't be updatable. You can usually replace `INNER JOIN` and `SUM` with `DSUM`, but I'm a little unclear on the question since the joined subquery doesn't get used – Erik A Apr 29 '18 at 21:04
Yes, i get an error: "Operation must use an updateable query" Anyone knows? – Juan.Queiroz Apr 29 '18 at 21:25
@Gordon Linoff [This question](https://stackoverflow.com/questions/62929358/update-table-field-by-summing-field-from-another-table) has similar issue. It says `Operation must use an updateable query`. Can you please have a look. – Harun24hr Jul 16 '20 at 11:21

score 2 · Accepted Answer · answered Apr 29 '18 at 21:58

2

You can use a DSUM to sum from a different table in an update query. Subqueries with aggregates won't work, because they're not updateable.

UPDATE t_client
SET [client_history] = DSUM("final_price", "T_PURCHASE", "client_id = " & client_id)

answered Apr 29 '18 at 21:58

Erik A

31,639
12
42
67

Access - query update with Inner Join

2 Answers2

Linked

Related