topic Re: Update DB table with logic in Application Development and Automation Discussions

Update DB table with logic

Former Member — Mon, 04 Jan 2010 13:02:35 GMT

HI All ,

I have table with records in DB which i need to modify from internal table via job.

if we start from scratch (fill the db table in the first time ) all the records should be valid

(column valid mark as abap_true)

in the next run if new data will come to the table the records should be valid automatically

and if some records are not occur in the second run the record in the table should be not valid

what is the best way to do that .

i give an example

in the first run i have users in the itab ( that update the db)

users roles valid_flag

user1 rol1 X

user1 rol2 X

user1 rol3 X

user2 rol1 X

user2 rol2 X

user2 rol3 X

This what i have in the internal table and what should be in the db table also .

in the next run i have in the itab

user1 rol1 X

user1 rol2 X

user1 rol4 X

user2 rol1 X

user2 rol5 X

user2 rol6 X

user3 rol1 X

user3 rol5 X

user3 rol3 X

so after the following run the table should look like .

users roles valid_flag

user1 rol1 X

user1 rol2 X

user1 rol3

user1 rol4 X

user2 rol1 X

user2 rol2

user2 rol3

user2 rol5 X

user2 rol6 X

user3 rol1 X

user3 rol5 X

user3 rol3 X

The old records are not deleted there are just mark as not valid and if we have new records

they are inserted to the table with valid flag .

What is the best way to do that .

Regards

Chris

Re: Update DB table with logic

Clemenss — Mon, 04 Jan 2010 13:51:53 GMT

Hi Chris,

it depends on the table size: If it's not too much you can read the whole table to internal table in memory, preferedly hashed or sorted table, then apply the logic creating one internal table for new records to be inserted and another one for records to be updated, finally use INSERT or UPDATE FROM TABLE.

There is no BEST method.

Regards,

Clemens

Re: Update DB table with logic

Jay71 — Mon, 04 Jan 2010 13:54:58 GMT

Hi,

Update the DB table either insert or update by using Update function module. Create a new function module and use that to update the table.

Thanks,

Jayaram

Re: Update DB table with logic

Former Member — Mon, 04 Jan 2010 18:08:02 GMT

HI Clemens ,

Can you help with the logic since i want to do read before update to internal table and do some compression between the table and i dont know how to proceed .

Thanks

Chris

Re: Update DB table with logic

Clemenss — Mon, 04 Jan 2010 18:11:35 GMT

Hi Chris,

I already gave the logic. The coding is done by you.

Regards,

Clemens

Re: Update DB table with logic

Former Member — Tue, 05 Jan 2010 06:15:55 GMT

HI All ,

I am facing problems with the code there is someone that can help with this code ?

Regards

Chris

Re: Update DB table with logic

Clemenss — Tue, 05 Jan 2010 07:54:08 GMT

Dear Chris,

if you are facing problems with the code, then where is the problem? If you are facing problems with writing the code, then this is not the right place to ask people to do the job you get paid for. This could be misunderstood as a misuse of this forum.

Regards,

Clemens

Re: Update DB table with logic

Former Member — Wed, 06 Jan 2010 13:58:26 GMT

HI ,

What i did is

1. copy the data to new internal table (read before update )

2. Run for all the users (i have tabe it_unq_bnames which store all the users unique )

3. Do loop on the copy db table and

Do read on the new data (it_new_usrs_data ) with key .

4.Update new table (lt_modif) which update the end table on DB

My question if it's good way to this or if there is another way to do that ,

performance is very important ?

Regards

Chris

**----Read before update ----*

  SELECT *
   FROM Zd_r_data
    INTO TABLE lt_copy_db
    WHERE valid EQ abap_true.
*---Update table

  LOOP AT it_unq_bnames INTO ls_unq_bnames.
    LOOP AT lt_copy_db ASSIGNING <fs_copy_db>.
      READ TABLE it_new_usrs_data ASSIGNING <fs_rs_new> WITH KEY
         bname = <fs_copy_db>-bname
         agr_name = <fs_copy_db>-agr_name
      IF sy-subrc EQ 0.
        ls_modif_rs-agr_name = <fs_copy_db>-agr_name.
        ls_modif_rs-bname = <fs_copy_db>-bname.
        ls_modif_rs-id = <fs_copy_db>-id.
        ls_modif_rs-last_update = <fs_copy_db>-last_update.
        ls_modif_rs-mandt = <fs_copy_db>-mandt.
        ls_modif_rs-valid = abap_true.
      ELSE.
        ls_modif_rs-agr_name = <fs_copy_db>-agr_name.
        ls_modif_rs-bname = <fs_copy_db>-bname.
        ls_modif_rs-id = <fs_copy_db>-id.
        ls_modif_rs-last_update = <fs_copy_db>-last_update.
        ls_modif_rs-mandt = <fs_copy_db>-mandt.
        ls_modif_rs-valid = abap_false.
      ENDIF.
      APPEND ls_modif_rs TO lt_modif.
    ENDLOOP.
  ENDLOOP.

Edited by: Chris Teb on Jan 6, 2010 9:48 PM

Re: Update DB table with logic

Former Member — Wed, 06 Jan 2010 21:14:02 GMT

Hi Chris,

Wht I understood from ur query is u need the data to be updated which does not exist in internal table and insert the data tht is in the IT, if it is so then

loop at it into wa.

select single * from zdb_table where user = wa-user and roll = wa-roll.

if sy-subrc = 0.

wa-valid = ' '.

modify zdb_table from wa.

else.

insert wa to zdb_table.

endif.

Thanks.

Anmol

Re: Update DB table with logic

Former Member — Wed, 06 Jan 2010 21:52:13 GMT

HI Anmol

its not that simple ...

I have table with records in DB which i need to modify from internal table via job.

if we start from scratch (fill the DB table in the first time ) all the records should be valid

(column valid mark as abap_true)

in the next run if new data will come to the table (it_new_usrs_data) the new records should be valid automatically

and if some records are not occur (in it_new_usrs_data ) in the second run the record in the DB table should be not valid .

You can see the the example in my first post .

What i want to avoid the select single in loop because of performance the DB table can store more than 100000 records ...

Do you have diff idea ?

in the first run i have users in the itab ( that update the DB)

Assume that after the first run i this is the DB table that i read in the second run with the select to lt_copy_db

users roles valid_flag

user1 rol1 X

user1 rol2 X

user1 rol3 X

user2 rol1 X

user2 rol2 X

user2 rol3 X

In the next run of the job i have in the internal table it_new_usrs_data

user1 rol1 X

user1 rol2 X

user1 rol4 X

user2 rol1 X

user2 rol5 X

user2 rol6 X

user3 rol1 X

user3 rol5 X

user3 rol3 X

so after the following run the DB table should look like .

The o_ld records are not deleted_ there are just mark as not valid and if we have new records

they are inserted to the table with valid flag .

users roles valid_flag

user1 rol1 X

user1 rol2 X

user1 rol3

user1 rol4 X

user2 rol1 X

user2 rol2

user2 rol3

user2 rol5 X

user2 rol6 X

user3 rol1 X

user3 rol5 X

user3 rol3 X

Thanks

Chris

Edited by: Chris Teb on Jan 6, 2010 10:58 PM

Re: Update DB table with logic

SureshRa — Wed, 06 Jan 2010 23:26:30 GMT

Hi Chris,

1. First, you need to find the records which are in the db but not in your internal table. Store these records in a separate internal table difference_tab.

2. Loop through the it_new_users_data and whatever records found in the db table, update them as valid. If the record is not found, insert them as valid records. You can use MODIFY statement which would ensure either modify or insert for such records.

3. Finally update all records in the difference_tab as invalid in the db table.

Before worrying about performance, first choose the reliable logic which would work in all scenarios. After successful first round of testing, carry-out performance testing and tune if necessary.

As it is already pointed out, implementing code for above logic is your responsibility and you should not expect other forum members to work for you.

Cheers

Suresh