-2

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
developer
  • 1,401
  • 4
  • 28
  • 73
  • Does your table have `ID` and `Parent` column? How do you know which child rows have which parents? – Charlieface Feb 03 '22 at 22:17
  • That's a badly designed table if you're [attempting to make a tree](https://stackoverflow.com/questions/317322/optimized-sql-for-tree-structures). – Paul Feb 05 '22 at 11:39
  • Yes, understood. This is just an example, the real table has parent child ids to relate them. – developer Feb 07 '22 at 09:50

3 Answers3

1

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;

DBFiddle demo

Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39
  • 1
    yes, it does have an ID - this is just a simplistic view of the table. I will amend the query accordingly. Thanks. – developer Feb 04 '22 at 09:34
1
Update TableName
set     Qty = (select Sum(Qty) from TableName where Level<>0),
        Orders = (select Sum(Qty) from TableName where Level<>0 )
where Level = 0
Teja Goud Kandula
  • 1,462
  • 13
  • 26
0
UPDATE yourtablename set qty = 
(Select sum(qty) from yourtablename
Where level != 0),
orders = 
(Select sum(orders) from yourtablename
Where level != 0) 
where level = 0
Aman
  • 89
  • 4
  • 2
    Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? – Jeremy Caney Feb 04 '22 at 00:52