Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2002
    Posts
    39

    Unanswered: PL/SQL to do an insert or update

    I want to have a pl/sql in Oracle 8i to do and insert or update.

    I'd like it read information from TABLEA and insert or update TABLEB.

    TABLEA and TABLEB consists of a DATE_T column which has a date and AMOUNT column, which has totals.

    I'd like for the script to read TABLEA and either insert or update TABLEB based on date. So if the same date exists in TABLEB, i'd like for it to take teh current value in AMOUNT and add it to the existing value. I want to do summaries based on date.

    Thanks for any guidance and if there's a quicker method to do this besides using a PL/SQL, that would be great too.


    JE

  2. #2
    Join Date
    Feb 2004
    Posts
    108
    You can use "upsert", if on 9i - it's like a merge.
    Or else use traditional way - if exists then update else insert !

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: PL/SQL to do an insert or update

    If you have a unigue constraint on the table you can also use an exception. This would reduce the number of executions needed by the If statement.

    Begin

    Insert Into table
    Values();

    Exception

    When Dup_Value_On_Index then
    Update Table
    Set =
    Where;

    End;

    HIH

  4. #4
    Join Date
    Oct 2002
    Posts
    39
    THANKS!!!!

    This is what I needed..

  5. #5
    Join Date
    Oct 2002
    Posts
    39
    COULD YOU SHOW ME A REAL EXAMPLE? Would something like this work?

    Begin

    Insert Into table2
    Values(select mydate, amount from my table1);

    Exception

    When Dup_Value_On_Index then
    Update table2
    Set amount = table1.amount + amount
    Where
    mydate = table1.mydate
    ;

    End;

  6. #6
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    Try something like
    Code:
    Begin
    
       Insert Into table2
       Select date, amount 
       From table1
       Where date = something;
    
       Commit;
    
    Exception
       -- Insert failed, so update record
       When Dup_Value_On_Index Then
          Begin
              Update table2
              Set   amount = amount + -- add amount from sub query
                     (Select t.amount 
                      From table1 t
                     Where t.date = something)
              Where date = something;
              Commit;
          End; -- end exception
    
    End;  -- end program

    HIH

  7. #7
    Join Date
    Oct 2002
    Posts
    39
    carola,

    This allows me to update a single data a time...

    is there a way to do multiple dates... for teh date option, I'd like to do a <='sysdate'.. .or something....

    this way... it would update all dates in my table2... and not just a singel one... thanks for any info...

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    How about:
    PHP Code:
    begin
      update tableb
      set 
    (col1,col2,...) = (select col1,col2, ...
                             
    from tablea 
                             where tablea
    .id tableb.id)
      
    where id in (select id from tablea);

      
    insert into tableb(idcol1col2, ...)
        
    select idcol1col2, ...
        
    from tablea
        where id in
          
    select id from tablea
            minus
            select id from tableb
         
    );
    end;


  9. #9
    Join Date
    Oct 2002
    Posts
    39
    update tableB
    set (amount) = (select amount
    from tableA
    where tableA.mydate = tableB.mydate)
    where mydate in (select mydate from tableA);


    This still would not take my current value in TABLEB and increment it..

    For example...

    TABLEA
    mydate amount
    -------- ---------
    3/10 200


    TABLEB
    mydate amount
    -------- ---------
    3/10 1000


    after my update.. my tableB should look like this..


    TABLEB
    mydate amount
    -------- --------
    3/10 1200


    My primary key being "mydate"

    Thanks!
    JE

  10. #10
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    Need to add the amount in

    Update tableB
    set amount = AMOUNT + (select amount
    from tableA
    where tableA.mydate = tableB.mydate)
    where mydate in (select mydate from tableA);

    Code:
    SQL>create table tableA (
        mydate date,
    	amount number);
    	
    SQL>create table tableB(
        mydate date,
    	amount number);
    	
    SQL>insert into tablea values (trunc(sysdate),200);
    SQL>insert into tablea values (trunc(sysdate -1 ),400);
    SQL>insert into tableb values (trunc(sysdate),1000);
    SQL>insert into tableb values (trunc(sysdate -1 ),1200);
    SQL> select * from tableA;
    
    MYDATE        AMOUNT
    --------- ----------
    22-MAR-04        200
    21-MAR-04        400
    
    SQL> select * from tableB;
    
    MYDATE        AMOUNT
    --------- ----------
    22-MAR-04       1000
    21-MAR-04       1200
    
    SQL> update tableB
      2  set amount = amount + (select amount
      3  from tableA
      4  where tableA.mydate = tableB.mydate)
      5  where mydate in (select mydate from tableA);
    
    2 rows updated.
    
    SQL> select * from tableB;
    
    MYDATE        AMOUNT
    --------- ----------
    22-MAR-04       1200
    21-MAR-04       1600

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    update tableB
    set (amount) = (select tablea.amount+tableb.amount
    from tableA
    where tableA.mydate = tableB.mydate)
    where mydate in (select mydate from tableA);

  12. #12
    Join Date
    Oct 2002
    Posts
    39
    This works perfect, but what if I have multiple days in tableA..
    like...

    mydate amount
    -------- ---------
    3/10 100
    3/10 200
    3/11 100


    If I use this update method, it gives an error that it returns more than a single row..

    Thanks for all the help!!!
    je

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    How about:

    update tableB
    set (amount) = (select sum(tablea.amount)+tableb.amount
    from tableA
    where tableA.mydate = tableB.mydate)
    where mydate in (select mydate from tableA);

  14. #14
    Join Date
    Oct 2002
    Posts
    39
    The update works for me now.... thanks!!!

    Though I encountered an issues with my insert...

    I have multiple fields for my primary key.... how can I insert into a table with multiple primary keys...

    Hereis how I have it...

    insert into tableB
    select trunc(mydate), myname, sum(amount) from tableA where trunc(mydate) not
    in (select trunc(mydate) from tableB )
    group by trunc(mydate), myname;
    commit;


    In the " where trunc(mydate) not in (select....)" I want to add another field...

    like... "where trunc(mydate), myname not in (select..."


    Thanks!

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    where (trunc(mydate), myname) not in (select...

Posting Permissions

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