更新查询以基于另一个表中的值更改一个表中的列的现有值 [英] Update query to change existing values for a column in one table based on values from another table

问题描述

我正在使用Microsoft Access，共有3个表:贷方，订单和帐簿，如下所示.我需要创建一个更新查询，根据他们订购的书和每本书的相应学分金额来更新每个学生的现有学分数.

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.

例如，学生B-17以24个学分开始，但是在更新查询之后，应将学生的学分更改为32.

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

贷方表

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

订单表

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

Books Table

Books Table

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

这是我正在尝试的方法，但是我在Access中不断收到语法错误.

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];

推荐答案

您可以尝试使用TEMP表存储第二个和第三个表中的数据.这样做:1)用两列创建另一个第四表(即Temp):Student ID和Credits； 2)运行此脚本:

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)开始更新:

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

4)保存并完成！

有用:1)"操作必须使用MS Access中的可更新查询"错误； 2) MS Access数据库( 2010)如何通过查询设计器创建临时表/过程/视图

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

这篇关于更新查询以基于另一个表中的值更改一个表中的列的现有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！