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

    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
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    update inventory a
    set (a.lastyear,a.currentyear) =
    (select sum(decode(to_char(b.trandate,'YYYY'),'2012',b.quantity,0)),
              sum(decode(to_char(b.trandate,'YYYY'),'2013',b.quantity,0))
    from matusetrans b
    where a.itemnum = b.itemnum);
    Last edited by beilstwh; 04-17-13 at 10:44.
    Bill
    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
  •