0

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?

2
  • Do you also require new inventory items to be inserted into the current table from the new table?
    – Philᵀᴹ
    Commented Mar 2, 2015 at 9:08
  • Is there a unique constraint on ItemName, in both tables? Commented Mar 2, 2015 at 11:50

1 Answer 1

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.

1
  • 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); Commented Mar 11, 2015 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.