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 |
CodePudding user response：
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;
CodePudding user response：
Update TableName
set Qty = (select Sum(Qty) from TableName where Level<>0),
Orders = (select Sum(Qty) from TableName where Level<>0 )
where Level = 0
CodePudding user response：
UPDATE yourtablename set qty =
(Select sum(qty) from yourtablename
Where level != 0),
orders =
(Select sum(orders) from yourtablename
Where level != 0)
where level = 0