根据不同表中的值更新表中的值 [英] Updating values in a table based on the values from a different table

问题描述

我很抱歉我的标题含糊不清，因为我总是很难描述在 SQL 方面需要做什么.我正在使用 microsoft access，我总共有 3 个表:Credits、Orders 和 Books，如下所示.我需要创建一个更新查询，根据他们订购的书籍和每本书对应的学分数量更新每个学生的现有学分数.

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.

例如，学生 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

书桌

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

我需要更新现有记录，而不是将其更改为将来会更新订单的位置.

I need to update the existing records, not change it to where it will update future orders.

推荐答案

需要在触发器中进行，这里更新只访问刚刚添加的订单，(新插入的订单)，而不是所有订单之前添加的.

You need to do this in a trigger, where the update is only accessing the orders that have just been added, (newly inserted orders), and not all orders previously added.

Update credits c set 
  credits = credits + 
    (Select sum(credits) from orders
     where student_id = c.student_Id)

或者，您需要标记orders表中的每一行是否已经被计入credits表.

Or, you need to flag each row in the orders table as to whether it has already been counted into the credits table.

 Update credits c set 
  credits = credits + 
    (Select sum(credits) from orders
     where student_id = c.student_Id 
        and added = 0)

然后设置标志表示订单已添加.

and then set the flag to indicate that the order has been added.

 Update orders set added = 1 
 where added = 0

这篇关于根据不同表中的值更新表中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！