Cannot update a table using a simple inner join

Question

I have 2 tables in access 2007. See attached picture to see the structure of the tables and the expected result.

I am trying to update the quantity field (ITQTY) in TABLE_BLNC by summarizing all the quantity field (LOCQTY) from TABLE_DTL for same items (LOITNBR=ITNBR). In TABLE_BLNC, the item is unique while in TABLE_DTL, the item can be in multiple records.

My query is:

UPDATE TABLE_BLNC INNER JOIN 
(
SELECT LOITNBR, Sum(LOCQTY) AS SumOfLOCQTY FROM TABLE_DTL GROUP BY LOITNBR) AS DTL 
ON TABLE_BLNC.ITNBR=DTL.LOITNBR SET TABLE_BLNC.ITQTY = DTL.SumOfLOCQTY;

I am getting the error: Operation must use an updateable query.

Syntax is wrong for an update using a join in T-SQL(Access, SQL server etc) - try looking at this answer and see if it helps http://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql — Takarii, Feb 01 '16 at 08:17
I'd try `UPDATE TABLE_BLNC SET ITQTY = (select sum(.... WHERE ... TABLE_BLNC.ITNBR=DTL.LOITNBR...` — jarlh, Feb 01 '16 at 08:32
UPDATE TABLE_BLNC SET ITQTY= (SELECT Sum(LOCQTY) AS SumOfLOCQTY FROM TABLE_DTL GROUP BY TABLE_DTL.LOITNBR WHERE TABLE_BLNC.ITNBR = TABLE_DTL.LOITNBR); Is it what you meant? I am getting syntax error — ehh, Feb 01 '16 at 08:40
Must `ITQTY` be a field which exists in the table? Can you simply derive it when needed with a `SELECT` query like this? `SELECT TABLE_BLNC.ITNBR, Nz(sub.SumOfLOCQTY, 0) AS [ITQTY] FROM TABLE_BLNC LEFT JOIN (SELECT LOITNBR, Sum(LOCQTY) AS SumOfLOCQTY FROM TABLE_DTL GROUP BY LOITNBR) AS sub ON TABLE_BLNC.ITNBR=sub.LOITNBR;` — HansUp, Feb 01 '16 at 17:45

HansUp · Accepted Answer · 2016-02-01T18:24:00.903

1

Domain Aggregate functions can be useful when Access complains that an UPDATE is not updateable. In this case, use DSum() ...

UPDATE TABLE_BLNC
SET ITQTY = 
    DSum("LOCQTY", "TABLE_DTL", "LOITNBR='" & ITNBR & "'");

Index TABLE_DTL.LOITNBR for optimum performance.

edited Feb 01 '16 at 18:24

answered Feb 01 '16 at 15:14

HansUp

95,961
11
77
135

This is the only workable suggestion so far, but I hesitate to endorse it because Domain Aggregate functions don't perform well for large data sets. – kismert Feb 01 '16 at 16:49
I think 'optimum' is overstating the benefit here. While indexing will likely improve performance with a Domain Aggregate function, it still is less efficient than a summary query. Maybe say 'better'? – kismert Feb 01 '16 at 18:21

kismert · Answer 2 · 2016-02-01T18:11:55.827

One of the great annoyances of Access SQL is its inability to update a table from an non-updatable source. Non-updatable sources include read-only links to ODBC tables, and GROUP BY (summary) queries.

What I always do is:

Copy the structure of TABLE_BLNK to a temp table: TABLE_BLNK_temp.
In your code, first delete the temp:
```
DELETE * FROM TABLE_BLNK_temp;
```

Insert the result of your summary query into temp:

INSERT INTO TABLE_BLNK_temp (ITNBR, ITQTY) 
SELECT LOITNBR, Sum(LOCQTY) AS SumOfLOCQTY 
FROM TABLE_DTL GROUP BY LOITNBR;

Update TABLE_BLNK from TABLE_BLNK_temp:

UPDATE TABLE_BLNC INNER JOIN TABLE_BLNK_temp AS t 
    ON TABLE_BLNC.ITNBR = t.ITNBR
SET TABLE_BLNC.ITQTY = t.ITQTY;

While it is an extra step or two, this approach:

Always works
Is more performant than Domain Aggregate functions for larger datasets

Cannot update a table using a simple inner join

2 Answers2