I would like to share a Task example to update the parent table with aggregated values from the child table.
The tables used in this example are not ideal but anyway, they showcase the idea.
For example, there is the ‘Students’ table that stores unique students.
Students can make payments so the ‘Payments’ table stores data about the payments made by students.
These tables are linked with the popular one-to-many type of relationships.
Use case: I need to calculate the sum of payments per student to store in the Students table (in the 'Payments_sum' field). Let`s say recalculation on a daily basis is acceptable.
The intuitive way to create a Task for this use case is the following: tables are joined, and the SUM() function is added.
However, this Task is not valid because in SQL it is impossible to use aggregate functions in update SET list.
Error message: An aggregate may not appear in the set list of an UPDATE statement. (error code: SQL157)
You can post now and register later.
If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.
Question
CoopperBackpack
I would like to share a Task example to update the parent table with aggregated values from the child table.
The tables used in this example are not ideal but anyway, they showcase the idea.
For example, there is the ‘Students’ table that stores unique students.
Students can make payments so the ‘Payments’ table stores data about the payments made by students.
These tables are linked with the popular one-to-many type of relationships.
Use case: I need to calculate the sum of payments per student to store in the Students table (in the 'Payments_sum' field). Let`s say recalculation on a daily basis is acceptable.
The intuitive way to create a Task for this use case is the following: tables are joined, and the SUM() function is added.
However, this Task is not valid because in SQL it is impossible to use aggregate functions in update SET list.
Error message: An aggregate may not appear in the set list of an UPDATE statement. (error code: SQL157)
Link to comment
Share on other sites
1 answer to this question
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.