从一个表到另一表复制的多个行中的Oracle

Question

表1

Item ---- Qauntity ---- Code 
123 1 --- 10  --- 123 
123 2 --- 20  --- 123 
123 3 --- 30  --- 123 
653 3 --- 60  --- 345 
653 2 --- 30  --- 345 
653 4 --- 20  --- 345 
967 3 --- 10  --- 967 
967 2 --- 20  --- 967 
967 1 --- 30  --- 967 

表2：

Code -- Qauntity 
123 --  40 
345 --  30 
444 --  10 
234 --  20 
653 --  60 

我需要由代码从表1和更新以获得与组总和（数量）在表2中，如果代码存在，则插入一个新行。剩余的行保留在表2中。如何为以下方案编写oracle plsql查询？

感谢

Answer 1

使用MERGE你能做到这一点

merge into table2 t2 using (select code, quantity from table1) t1 on (t2.code = t1.code) 
when not matched then insert (code,quantity) values (t1.code,t1.qty) 
when matched then update set quantity = quantity+t1.quantity; 

Answer 2

您可以使用merge为“更新插入一排”（更新或插入）合并源可以是一个子查询，你可以group by Code和计算数量的总和：

merge into Table2 t2 
using (
     select Code 
     ,  sum(Quantity) as SumQuantity 
     from Table1 
     group by 
       Code 
     ) t1 
on  (t1.Code = t2.Code) 
when not matched then 
     insert (Code, Quantity) 
     values (t1.Code, t1.SumQuantity) 
when matched then 
     update set Quantity = t1.SumQuantity; 

Example at SQL Fiddle.