I have the following table:
| Category | Qty | Orders | Level |
|---|---|---|---|
| Product | 0 | ||
| Part 1 | 2 | 4 | 1 |
| Part 2 | 3 | 5 | 1 |
| Part 1.1 | 4 | 6 | 2 |
| Part 1.2 | 5 | 7 | 2 |
How can I update the above table’s Level 0 row with sum of Qty and Orders (excluding Level 0’s Qty and Orders)?
i.e.
| Category | Qty | Orders | Level |
|---|---|---|---|
| Product | 14 (i.e. 2+3+4+5) | 22 (i.e. 4+5+6+7) | 0 |
| Part 1 | 2 | 4 | 1 |
| Part 2 | 3 | 5 | 1 |
| Part 1.1 | 4 | 6 | 2 |
| Part 1.2 | 5 | 7 | 2 |
Advertisement
Answer
You could use an Update statement that uses a subquery or CTE, but I don’t think in reality your table could be so simplistic without any Id (for example an id that would link all those Part* to a particular Product. Anyway, as is:
Update myTable set Qty = agg.Qty, Orders=agg.Orders from (Select Sum(Qty) Qty, Sum(Orders) Orders from myTable where level > 0) agg where level=0;