Update a record using the sum of it's value and another

Question

I want to update the values of a record using itself and the values from another row without using a bunch of selects in the set portion of the query to combine the 2. Assume you already know the primary keys of both rows.

Here's an example of what should happen before and after:

Before:

| pk_id|allocated|purchased|installed|
|    10|        2|        5|       10|
|     3|        8|        6|        2|
|    11|        2|        6|        7|

After:

| pk_id|allocated|purchased|installed|
|    10|        2|        5|       10|
|     3|        8|        6|        2|
|    11|        4|       11|       17|

I want to NOT have to do something like this:

UPDATE Example
set allocated = (select allocated from Example where pk_id = 10) 
                + (select allocated from Example where pk_id = 11),
    purchased = (select purchased from Example where pk_id = 10) 
               + (select purchased from Example where pk_id = 11),
    installed = (select installed from Example where pk_id = 10) 
               + (select installed from Example where pk_id = 11)
    WHERE pk_row = 11

All of those selects seem REALLY unnecessary, but I can't think of a better way.

Do want to accumulate *two* rows or *all* rows with an `pk_id` smaller or equal to the current one? — Carsten Massmann, Jun 26 '15 at 15:07
The former. Aggregate the column's values from these 2 rows and store the results into the one you choose. — Luminous, Jun 26 '15 at 15:09
It could be any 2 rows in the table. They don't have to be beside each other. — Luminous, Jun 26 '15 at 15:12
possible duplicate of [Calculate a Running Total in SqlServer](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver) — Tab Alleman, Jun 26 '15 at 15:28
@TabAlleman This isn't a running total. This is a statistics table with a 1:1 relation with another table. — Luminous, Jun 26 '15 at 15:36
Your question sure makes it look like it's all coming from one table. You might want to edit to make it clearer for future readers. — Tab Alleman, Jun 26 '15 at 15:40
Well, your edit still suggests that you are only looking at two rows in the SAME table. That makes this a running total, but only of two rows, rather than all previous rows. If you are joining two tables that have a 1:1 relationship, that isn't clear in the question at all to me. (Everywhere you name a table in your question, it is always named "Example", so it sounds like there is only one table involved. Otherwise, there would be "Example1" and "Example2") — Tab Alleman, Jun 26 '15 at 15:47
This table holds running totals, but I'm not asking how to calculate that. The records are not related to each other. The link to the possible duplicate has answers for calculating a running total for the entire table. I'm asking for any two rows, and I'm using this to update multiple columns instead of just one. Being even more specific, I'm asking how can I write a query that has better performance than what I've provided. — Luminous, Jun 26 '15 at 20:09

score 1 · Accepted Answer · answered Jun 26 '15 at 15:18

1

If you know record keys:

DECLARE @Id1 INT = 10, @Id2 INT = 11

;WITH CTE AS
(
  SELECT @Id2 Id, SUM(allocated) Allocated, SUM(purchased) Purchased, 
         SUM(installed) Installed
  FROM YourTable
  WHERE pk_id IN (@Id1 ,@Id2)
)
UPDATE t SET allocated = c.Allocated, purchased = c.Purchased, 
             installed = c.Installed
FROM YourTable t
         JOIN CTE c ON t.pk_id = c.id

answered Jun 26 '15 at 15:18

Kaf

33,101
7
58
78

I was thinking you could sum the values together before joining them onto the row you chose. Didn't know how. AXMIM's answer is what I expected, but this answer is the response I was hoping for. – Luminous Jun 26 '15 at 15:37

score 0 · Answer 2 · answered Jun 26 '15 at 15:09

That certainly isn't the most efficient method of this. Here is another way that cuts the queries in half. The ugly part of this is that you are storing aggregate data for groups in each row. This is a nightmare to maintain and not normalized properly.

UPDATE Example
set allocated = (select sum(allocated) from Example where pk_id in (10, 11)),
    purchased = (select sum(purchased) from Example where pk_id in (10, 11)),
    installed = (select sum(installed) from Example where pk_id in (10, 11))
WHERE pk_row = 11

score 0 · Answer 3 · answered Jun 26 '15 at 15:10

You can update the row by joining it to the row to add. Like this :

UPDATE Example
SET allocated = Example.allocated + OtherRow.allocated,
    purchased = Example.purchased + OtherRow.purchased,
    installed = Example.installed + OtherRow.installed 
FROM Example
INNER JOIN Example AS OtherRow ON OtherRow.pk_row = 10
WHERE Example.pk_row = 11

score 0 · Answer 4 · answered Jun 26 '15 at 18:04

This way, you can SUM before joining. I believe summing before joining come handy only if you SUM more than one row into the other one. Otherwise, I don't see the point of using this approach. Especially since it's querying twice the same row instead of just once.

DECLARE @Pk10 INT = 10, 
        @Pk11 INT = 11

UPDATE Example
SET allocated = OthersRows.allocatedTotal,
    purchased = OthersRows.purchasedTotal,
    installed = OthersRows.installedTotal 
FROM Example
INNER JOIN (SELECT  PkToUpdate = @Pk11,
                    allocatedTotal = SUM(allocated),
                    purchasedTotal = SUM(purchased),
                    installedTotal = SUM(installed)
            FROM Example
            WHERE Example.pk_row IN (@Pk10, @Pk11)) AS OthersRows ON Example.pk_row = OthersRows.PkToUpdate

This solution does the same as kaf's but doesn't use CTE. However, using CTE is probably better for the reader as it's get rid of one indentation level. — AXMIM, Jun 26 '15 at 18:11

Update a record using the sum of it's value and another

4 Answers4