Oracle SQL Summing Values From Two Different Tables

Question

Hey all so I am trying to add to (or subtract from) a column of different values based on a separate column in another table. Here is a example of what I am tying to do:

Table 1: (Current Inventory)

ItemName | Quantity
----------|----------
Baseball | 5
Football | 10
Jersey | 3

Table 2: (New Shipment)

ItemName | Quantity
----------|----------
Baseball | 70
Football | 100
Jersey | 50

How can I update my all items in Table 1 (current inventory) by adding Table 2's quantity to them?

• Philᵀᴹ: Do you also require new inventory items to be inserted into the current table from the new table?
• ypercubeᵀᴹ: Is there a unique constraint on ItemName, in both tables?

Answer 1

You can do this using an UPDATE:

UPDATE inventory i
 SET quantity = ( SELECT s.quantity+i.quantity 
 FROM shipment s
 WHERE s.itemname = i.itemname );

or a MERGE:

MERGE INTO inventory
USING 
(
 SELECT itemname, quantity
 FROM shipment
) ship ON (ship.itemname = inventory.itemname)
WHEN MATCHED THEN UPDATE 
 SET inventory.quantity = ship.quantity + inventory.quantity;

Example SQL Fiddle here if you want to play.

• ypercubeᵀᴹ: Nice. The first may need adjustments, if the items appear multiple times in shipment (or none, so nulls will appear due to SUM). Something like: SET quantity = quantity + COALESCE( ( SELECT SUM(s.quantity) FROM shipment s WHERE s.itemname = i.itemname ), 0);