Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2012
    Posts
    18

    Unanswered: insert in target table and then update the source table field in oracle

    In Oracle, I have a requirement where in I need to insert records from Source to Target and then update the PROCESSED_DATE field of source once the target has been updated.

    1 way is to use cursors and loop row by row to achieve the same.

    Is there any other way to do the same in an efficient way?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Insert into target select * from source;
    update source set processed_date = sysdate;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2012
    Posts
    18
    For doing this, let me explain further more about my problem statement.

    In my source, initially my PROCESSED_DATE = NULL (Ex:- say 100 rows out of 800 rows in the source table)

    For every half an hour, my job executes and it will fetch those records whose PROCESSED_DATE = NULL.

    In this case, Fetch the 100 records and then insert into Target table(based on some business logic).

    Update these 100 records in the source table to PROCESSED_DATE = SYSDATE.


    If i use the below queries,

    <Insert into target select * from source where processed_Date is null;
    update source set processed_date = sysdate where <Condition to be given>
    >

    What should be the condition given here? As we can see that only those records of source need to be updated which are inserted in the target(In our case, the 100 records)

    Hope i am clear now.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Well then run:
    Code:
    update source 
      set processed_date = sysdate 
    where processed_date is null
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  5. #5
    Join Date
    Jun 2012
    Posts
    18
    Thats absolutely correct.

    update source
    set processed_date = sysdate
    where processed_date is null


    But my query is i can not go and update all the records whose PROCESSED_DATE is null .

    The reason is like "suppose it took 1 minute for me to insert the records into Target table" . Then i am trying to update the records in Source table. By that time, suppose 30 new records got inserted, then i am updating 130 records instead of 100.

    Could you please clarify this?

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Code:
    declare
    date_stamp date;
    begin
    select sysdate
    into date_stamp
    from dual;
    
    update source set processed_date = date_stamp
    where procedded_date is null;
    Insert into target 
    select * from source
    where processed_date = date_stamp;
    commit;
    end;
    /
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Jun 2012
    Posts
    18

    insert in target table and then update the source table field in oracle

    Thats a perfect solution to my query.

    Thanks a lot for your help

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Just as an aside, you have one more option. Have an trigger on source that would update the processed_date and insert into target on insert into source.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Jun 2012
    Posts
    18

    insert in target table and then update the source table field in oracle

    Actually the data need to be populated in the target table on a periodical basis. There is no need to populate the data on online basis for this requirement.

    Once again, thanks for all your help.

Tags for this Thread

Posting Permissions

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