Update query to change existing values for a column in one table based on values from a different table

Question

I apologize for my title being vague as I always have a difficult time describing what needs to be done when it comes to SQL. I am using microsoft access and I have a total of 3 tables: Credits, Orders, and Books which are shown below. I need to create an update query that updates the existing Number of Credits for each student based on the books that they have ordered and the corresponding amount of credits for each book.

For instance, student B-17 starts with 24 credits but after the update query it should change the student's credits to 32.

Credits Table

Student ID    Number of Credits
B-17          24
F-59          30

Orders Table

Student ID    Book ID
B-17          101
B-17          102
F-59          101
F-59          105

Books Table

Book ID    Book Title    Credits
101        English I     3
102        Accounting    5
105        Calculus      5

This is what I am trying but I keep getting a syntax error in Access.

UPDATE Credits c
SET [Number of Credits] = [Number of Credits] + (SELECT SUM(Credits)
FROM Orders o, Books b ON
o.[Book ID] = b.[Book ID] WHERE 
c.[Student ID] = o.[Student ID])
WHERE c.[Student ID] = o.[Student ID];

Possible duplicate of [Update query to change existing values for a column in one table based on values from another table](https://stackoverflow.com/questions/45526753/update-query-to-change-existing-values-for-a-column-in-one-table-based-on-values) — Gustav, Aug 06 '17 at 07:13
Why does Credits have to be a Table, and not a Select Query? — tahwos, Aug 07 '17 at 02:40

Gordon Linoff · Answer 1 · 2017-08-06T00:47:05.777

You have a comma with an ON. You can try:

UPDATE Credits as c INNER JOIN
       (SELECT o.[Student ID], SUM(b.Credits) as Credits
        FROM Orders as o INNER JOIN
             Books as b
             ON o.[Book ID] = b.[Book ID]
        GROUP BY o.[Student ID]
       ) ob
       ON c.[Student ID] = o.[Student ID]
    SET c.[Number of Credits] = c.[Number of Credits] + ob.Credits;

I often seem to have trouble getting join syntax to work with MS Access. Instead:

UPDATE Credits
    SET [Number of Credits]  = [Number of Credits] +
                               (SELECT SUM(b.Credits) as Credits
                                FROM Orders as o INNER JOIN
                                     Books as b
                                     ON o.[Book ID] = b.[Book ID]
                                WHERE o.[Student ID] = Credits.[Student ID]
                               );

For some reason it's now throwing a syntax error in the UPDATE statement — Dab Sauce, Aug 06 '17 at 00:45

score 0 · Answer 2 · answered Aug 06 '17 at 06:09

You can try to use TEMP table for storing your data from the 2nd and 3rd tables; Do like this: 1) create another 4th table (i.e. Temp) with two columns: Student ID and Credits; 2) run this script:

SELECT o.[Student ID], sum (b.Credits) as Credits INTO Temp FROM books b INNNER JOIN orders o on b.[Book ID] = o.[Book ID] GROUP BY o.[Student ID];

3) Start to Update:

UPDATE Credits c, Temp t set c.[Number of Credits] = c.[Number of Credits] + t.Credits WHERE c.[Student ID] = t.[Student ID];

4) Save and Finished!

Useful: 1) "Operation must use an updateable query" error in MS Access ; 2) MS Access database (2010) how to create temporary table/procedure/view from Query Designer

Update query to change existing values for a column in one table based on values from a different table

2 Answers2