1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

update column based on sum of data from another table

Discussion in 'SQL PL/SQL' started by ajay696, Apr 17, 2013.

  1. ajay696

    ajay696 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    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 sum(quantity) sum(quantity)
    AM1324 AM2 sum(quantity) sum(quantity)

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

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

    Any help...How to write an sql queries to update lastyear and currentyear columns with sum of quantities based on itemnum and location in Inventory table

    Thanks
     
  2. fsugeiger

    fsugeiger Guest

    Since i didn't have the physical tables I went ahead and used temp tables. Just replace the temp tables with the physical tables and you should be all set.



    CREATE GLOBAL TEMPORARY TABLE temp_MatUserTrans
    (
    ItemNum varchar(50),
    Location VARCHAR(50),
    Quantity number(9),
    TransDate date

    )
    ON COMMIT PRESERVE ROWS;


    INSERT INTO temp_MatUserTrans VALUES('AM1324','AM1',2, to_date('2013/3/04','yyyy/mm/dd'));

    INSERT INTO temp_MatUserTrans VALUES('AM1324','AM1',4, to_date('2012/12/05','yyyy/mm/dd'));

    INSERT INTO temp_MatUserTrans VALUES('AM1324','AM2',2, to_date('2013/2/06','yyyy/mm/dd'));

    INSERT INTO temp_MatUserTrans VALUES('AM1324','AM2',5, to_date('2012/11/06','yyyy/mm/dd'));


    CREATE GLOBAL TEMPORARY TABLE temp_Inventory
    (
    ItemNum varchar(50),
    StoreLoc VARCHAR(50),
    LastYearQty number(9),
    CurrentYearQty number(9)

    )
    ON COMMIT PRESERVE ROWS;

    INSERT INTO temp_Inventory VALUES('AM1324','AM1',null, null);

    INSERT INTO temp_Inventory VALUES('AM1324','AM2',null, null);

    -- check to confirm null
    select * from temp_Inventory;


    -- run update
    Update temp_Inventory i
    set (LastYearQty , CurrentYearQty)= (select sum(case when TransDate between to_date('2012/01/01','yyyy/mm/dd') and to_date('2012/12/31','yyyy/mm/dd') then quantity else 0 end) as LastYearQty,
    sum(case when TransDate between to_date('2013/01/01','yyyy/mm/dd') and to_date('2013/12/31','yyyy/mm/dd') then quantity else 0 end ) as CurrentYearQty
    from temp_MatUserTrans t
    where i.ItemNum = t.ItemNum and i.StoreLoc = t.Location
    group by ItemNum, Location)
    ;
    -- commit update
    commit;

    -- check updated values
    select * from temp_Inventory;

    -- clean up

    truncate table temp_MatUserTrans;
    truncate table temp_Inventory;

    drop table temp_MatUserTrans;
    drop table temp_Inventory;
     
  3. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    Use this sql:
    Code (SQL):

    SELECT  itemnum
           ,location
           ,SUM(nvl(quantity,0)) qty
           ,YEAR
    FROM
       (SELECT  m1.itemnum
               ,m1.location
               ,m1.quantity
               ,to_char(m1.transdate,'yyyy')    YEAR
        FROM    matusetrans m1
       )
    GROUP   BY  itemnum
               ,location
               ,YEAR
    ORDER   BY  1,2,4          
     
    I'll leave the update part to u. :hurray
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I would use that query as a starting point to create a view that will provide the necessary information for the updates:

    Code (SQL):
    SQL> CREATE TABLE matusetrans(
      2          itemnum varchar2(14),
      3          location varchar2(5),
      4          quantity        NUMBER,
      5          transdate DATE
      6  );
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE inventory(
      2          itemnum varchar2(14),
      3          storeloc varchar2(5),
      4          lastyear        NUMBER,
      5          currentyear     NUMBER
      6  );
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO matusetrans
      3  VALUES('AM1324','AM1',2,'12-4-12')
      4  INTO matusetrans
      5  VALUES('AM1324','AM1',2,'15-5-12')
      6  INTO matusetrans
      7  VALUES('AM1324','AM1',3,'10-6-12')
      8  INTO matusetrans
      9  VALUES('AM1324','AM1',4,'5-1-13')
     10  INTO matusetrans
     11  VALUES('AM1324','AM1',2,'13-3-13')
     12  INTO matusetrans
     13  VALUES('AM1324','AM2',3,'2-5-12')
     14  INTO matusetrans
     15  VALUES('AM1324','AM2',2,'12-7-12')
     16  INTO matusetrans
     17  VALUES('AM1324','AM2',1,'13-2-13')
     18  INTO inventory(itemnum, storeloc)
     19  VALUES('AM1324','AM1')
     20  INTO inventory(itemnum, storeloc)
     21  VALUES('AM1324','AM2')
     22  SELECT * FROM dual;
     
    10 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT * FROM inventory;
     
    ITEMNUM        STORE   LASTYEAR CURRENTYEAR
    -------------- ----- ---------- -----------
    AM1324         AM1
    AM1324         AM2
     
    SQL>
    SQL> CREATE OR REPLACE VIEW store_inventory_vw AS
      2  WITH qty_year AS(
      3          SELECT  itemnum
      4         ,location
      5         ,SUM(nvl(quantity,0)) qty
      6         ,YEAR
      7          FROM
      8          (SELECT  m1.itemnum
      9             ,m1.location
     10             ,m1.quantity
     11             ,to_char(m1.transdate,'rrrr') YEAR
     12          FROM    matusetrans m1
     13          )
     14          GROUP BY  itemnum
     15             ,location
     16             ,YEAR
     17          ORDER BY 1,2,4
     18  )
     19  SELECT q.itemnum, q.location, q.qty lyear, y.qty tyear
     20  FROM qty_year q JOIN qty_year y ON (y.itemnum=q.itemnum AND y.location=q.location)
     21  WHERE q.YEAR = to_char(to_number(to_char(sysdate, 'rrrr')) - 1)
     22  AND y.YEAR = to_char(sysdate, 'rrrr')
     23  /
     
    VIEW created.
     
    SQL>
    SQL> UPDATE inventory
      2  SET lastyear = (SELECT lyear FROM store_inventory_vw WHERE itemnum = inventory.itemnum AND location = inventory.storeloc),
      3      currentyear = (SELECT tyear FROM store_inventory_vw WHERE itemnum = inventory.itemnum AND location = inventory.storeloc)
      4  /
     
    2 ROWS updated.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT * FROM inventory;
     
    ITEMNUM        STORE   LASTYEAR CURRENTYEAR
    -------------- ----- ---------- -----------
    AM1324         AM1            7           6
    AM1324         AM2            5           1
     
    SQL>
    SQL> INSERT ALL
      2  INTO matusetrans
      3  VALUES('AM1325','AM1',2,'12-4-12')
      4  INTO matusetrans
      5  VALUES('AM1325','AM1',2,'15-5-12')
      6  INTO matusetrans
      7  VALUES('AM1325','AM1',3,'10-6-12')
      8  INTO matusetrans
      9  VALUES('AM1325','AM1',4,'5-1-13')
     10  INTO matusetrans
     11  VALUES('AM1325','AM1',2,'13-3-13')
     12  INTO matusetrans
     13  VALUES('AM1325','AM2',3,'2-5-12')
     14  INTO matusetrans
     15  VALUES('AM1325','AM2',2,'12-7-12')
     16  INTO matusetrans
     17  VALUES('AM1325','AM2',1,'13-2-13')
     18  INTO inventory(itemnum, storeloc)
     19  VALUES('AM1325','AM1')
     20  INTO inventory(itemnum, storeloc)
     21  VALUES('AM1325','AM2')
     22  SELECT * FROM dual;
     
    10 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT * FROM inventory;
     
    ITEMNUM        STORE   LASTYEAR CURRENTYEAR
    -------------- ----- ---------- -----------
    AM1324         AM1            7           6
    AM1324         AM2            5           1
    AM1325         AM1
    AM1325         AM2
     
    SQL>
    SQL> UPDATE inventory
      2  SET lastyear = (SELECT lyear FROM store_inventory_vw WHERE itemnum = inventory.itemnum AND location = inventory.storeloc),
      3      currentyear = (SELECT tyear FROM store_inventory_vw WHERE itemnum = inventory.itemnum AND location = inventory.storeloc)
      4  /
     
    4 ROWS updated.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> SELECT * FROM inventory;
     
    ITEMNUM        STORE   LASTYEAR CURRENTYEAR
    -------------- ----- ---------- -----------
    AM1324         AM1            7           6
    AM1324         AM2            5           1
    AM1325         AM1            7           6
    AM1325         AM2            5           1
     
    SQL>
    Notice that when the source data changed the view data changed to reflect that, providing current numbers for your updates.
     
  5. ajay696

    ajay696 Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thank you for all your help.

    Finally query build and executed...............

    Thank you.............