Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2013

    Unanswered: update column based on sum of data from another table

    Hi We had two tables.

    Table 1: matusetrans

    ITEMNUM Location Quantity transdate
    AM1324 AM1 2 12-4-12
    AM1324 AM1 2 15-5-12
    AM1324 AM1 3 10-6-12
    AM1324 AM1 4 5-1-13
    AM1324 AM1 2 13-3-13
    AM1324 AM2 3 2-5-12
    AM1324 AM2 2 12-7-12
    AM1324 AM2 1 13-2-13

    Table 2: Inventory

    ITEMNUM STORELOC lastyear currentyear
    AM1324 AM1 need sum(quantity) here need sum(quantity)
    AM1324 AM2 need sum(quantity) here need sum(quantity)

    We have to update the last year and current year columns with sum of quantities for each item from matusetrans table at different location in Inventory table.

    we had nearly 13,000 records(itemnum's with different location) in inventory table in DB we have to update entire records.

    Any help...How to write an update query to achieve this

  2. #2
    Join Date
    Jun 2004
    Liverpool, NY USA
    update inventory a
    set (a.lastyear,a.currentyear) =
    (select sum(decode(to_char(b.trandate,'YYYY'),'2012',b.quantity,0)),
    from matusetrans b
    where a.itemnum = b.itemnum);
    Last edited by beilstwh; 04-17-13 at 09:44.
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts