Skip to Content
author's profile photo Former Member
Former Member

Update table and insert on the same time

HI All ,

I have Z DB table which i need to to update via program and i need to use the following process .

1. Assume the table have already user with his data and the process don't have changes in the user data ->dont do anything

2. If the user have new data -> mark the old entry as not valid and insert new entry

3. if there is new user is coming on the update process insert him to the new table and mark the entry as valid

The DB table have this fields as :

1. mandt  "key
2. uuid     "key
3. user 
4 . user data 
5 . time stemp
6. valid_entry  " yes or no

What is the the best solution to handle this issue ?

Regards

Chris

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Nov 13, 2009 at 08:12 AM

    I think you have answered the question yourself by describing the process step by step. Simply transfer the logic to the code.

    select * ....  where ...  "check existence of data for the user
    if sy-subrc ne 0 .   "if data doesn't exists
       if new_user = 'X'.
          "new user is coming, new data requeired
           insert ... "insert data with field valid_entry = 'YES'.  
        else.
          "old user, but new data
          update .... set valid_entry = 'NO'.  "first update old entry to invalid
          insert    .... "then insert new data with valid_entry = 'YES'.
        endif.
    endif.
    

    This should suffice

    Regards

    Marcin

    Add a comment
    10|10000 characters needed characters exceeded

    • No, I think as far as performance is concerned, you should avoid select statement within loop. This is because for every single record from internal table with user, there has to be established separate session with DB. This has impact on the performace which is low then.

      I suggest to get these data at once with select ... for all entries...into table ... (so you connect with DB only once) and you store all the record locally in internal table. Then simply loop at that table and check each record for existence. That will certainly improve the performance.

      Regards

      Marcin

      another thing that i consider is to use for the design 2 DB tables since i can have user with 5 records of data

      like

      user1 data1

      user1 data2

      user1 data3

      one for the user and another table with user and his entries (like header and lines ) what do you think ?

      This is ok from design perspective, but I think it introduces new table to handle with. If there is no to much data about user to store, there is not point in creating separate table to hold that information. Conversely if data about user is huge (like 10-20 fields) you can have them in some "header" table like you said.

      Edited by: Marcin Pciak on Nov 13, 2009 9:36 AM

  • author's profile photo Former Member
    Former Member
    Posted on Nov 13, 2009 at 08:32 AM
    Select * from Ztable into wa_ztable where uuid = id.
    
        if sy-subrc = 0.  ""already existing user.
    
            if wa_ztable <> user_data.  ""the user data is different from the database entry.
    
               wa_ztable-valid_entry = 'N'.
    
               update ztable from wa_table transporting all fields. "making the record as not a valid entry
    
               update ztable from user_data transporting all fields.  " inserting a new record.
    
            endif.
    
         else.
    
           update ztable from user_data transporting all fields.  " inserting a new record.
    
         endif.
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.