SQL - Set column value to the SUM of all references

Question

I want to have the column "CurrentCapacity" to be the SUM of all references specific column.

Picture of DB

Lets say there are three rows in SecTable which all have FirstTableID = 1. Size values are 1, 1 and 3. The row in FirstTable which have ID = 1 should now have a value of 5 in the CurrentCapacity column.

How can I make this and how to do automatically on insert, update and delete?

Thanks!

FirstTable

+----+-------------+-------------------------+
| ID | MaxCapacity | CurrentCapacity         |
+----+-------------+-------------------------+
| 1  | 5           | 0 (desired result = 5)  |
+----+-------------+-------------------------+
| 2  | 5           | 0                       |
+----+-------------+-------------------------+
| 3  | 5           | 0                       |
+----+-------------+-------------------------+

SecTable

+----+-------------------+------+
| ID | FirstTableID (FK) | Size |
+----+-------------------+------+
| 1  | 1                 | 2    |
+----+-------------------+------+
| 2  | 1                 | 3    |
+----+-------------------+------+

Please provide sample data and desired results *as text tables* in the question. Also explain why simply using a `join`/`group by` is not sufficient for your application. Duplicating data is not usually a good idea. — Gordon Linoff, Feb 06 '20 at 11:31
@GordonLinoff I have added text tables. I think its a trigger I want to create. So when I insert, update or delete in the SecTable, the CurrentCapacity should be calculated to correct value. — Linus, Feb 06 '20 at 11:53
It looks like what you want is covered here: https://stackoverflow.com/questions/2769007/formula-for-computed-column-based-on-different-tables-column You can create a user defined function to get the value and then use that user defined function in a calculated column — James S, Feb 06 '20 at 12:03
"UPDATE FirstTable SET FirstTable.CurrentCapacity = (SELECT SUM(SecTable.Size) FROM SecTable WHERE FirstTableID = 1);" This query do the job. But I want this to happen automatic and "WHERE FirstTableID = 1" is not always 1 because when CurrentCapacity is equal to MaxCapacity, I will use another "FirstTableID" — Linus, Feb 06 '20 at 12:10

score 1 · Answer 1 · answered Feb 06 '20 at 12:04

In general, a view is a better solution than trying to keep a calculated column up-to-date. For your example, you could use this:

CREATE VIEW capacity AS
SELECT f.ID, f.MaxCapacity, COALESCE(SUM(s.Size), 0) AS CurrentCapacity
FROM FirstTable f
LEFT JOIN SecTable s ON s.FirstTableID = f.ID
GROUP BY f.ID, f.MaxCapacity

Then you can simply

SELECT *
FROM capacity

to get the results you desire. For your sample data:

ID  MaxCapacity     CurrentCapacity
1   5               5
2   5               0
3   5               0

Demo on SQLFiddle

score 0 · Answer 2 · answered Feb 06 '20 at 15:13

0

Got this question to work with this trigger:

CREATE TRIGGER UpdateCurrentCapacity
ON SecTable
AFTER  INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON
    DECLARE @Iteration INT
    SET @Iteration = 1
    WHILE @Iteration <= 100
    BEGIN
        UPDATE FirstTable SET FirstTable.CurrentCapacity = (SELECT COALESCE(SUM(SecTable.Size),0) FROM SecTable WHERE FirstTableID = @Iteration) WHERE ID = @Iteration;
        SET @Iteration = @Iteration + 1
    END
END
GO

answered Feb 06 '20 at 15:13

Linus

3
2

Just keep in mind it will only work for 100 rows. If the table would grow, you'd have to update this variable and this loop would have to run each time any row is changed in the second table. With SQL you want to avoid doing RBAR and and programmatic approaches. What you could do is merge the view into your table or do an UPDATE for performance. – Agneum Feb 08 '20 at 18:13

Agneum · Answer 3 · 2020-02-06T15:52:10.373

Personally, I would not use a trigger either or store CurrentCapacity as a value since it breaks Normalization rules for database design. You have a relation and can already get the results by creating a view or setting CurrentCapacity to a calculated column.

Your view can look like this:

SELECT Id, MaxCapacity, ISNULL(O.SumSize,0) AS CurrentCapacity 
FROM dbo.FirstTable FT
OUTER APPLY
(
    SELECT ST.FirstTableId, SUM(ST.Size) as SumSize FROM SecTable ST
    WHERE ST.FirstTableId = FT.Id
    GROUP BY ST.FirstTableId
) O

Sure, you could fire a proc every time a row is updated/inserted or deleted in the second table and recalculate the column, but you might as well calculate it on the fly. If it's not required to have the column accurate, you can have a job update the values every X hours. You could combine this with your view to have both a "live" and "cached" version of the capacity data.

Thanks for your answer. This is for a school project and not in a professional environment. Im building a Console app in C# and want to update the CurrentCapacity everytime I run a query to the DB. For now I will probably use the trigger solution. Thanks for the input! — Linus, Feb 06 '20 at 18:18

SQL - Set column value to the SUM of all references

3 Answers3