topic Re: Handling update and insert in a database table in Application Development and Automation Discussions

Handling update and insert in a database table

Former Member — Wed, 28 May 2008 09:24:12 GMT

Hi guys,

I have some data in a database table which I created.Every day some new records come into the table.what i want is that if the account no of the new record which comes say today already exists in the table hen I do not insert a new record but only update the value field related to the account no.

Being more explicit:

say my table has two columns

acc no value.

10 3500

Now as for the existing account no 10 say a new value 300 comes in I want that a new field is not created rather the value is made to 3500+300.

and if an accno 20 comes which is not there in table a new row is aded for that.

Any suggestions would be appreciated.

Thanks

Re: Handling update and insert in a database table

Former Member — Wed, 28 May 2008 09:27:43 GMT

Hi,

if you work with modify,

the system makes a insert, if no record exists and an update, if one is there.

Regards

Nicole

Re: Handling update and insert in a database table

Former Member — Wed, 28 May 2008 09:29:35 GMT

hi,

how is the table updated

if its through a program use collect statement

or at end of

then use modify to update the database table

regards

mahesh

Re: Handling update and insert in a database table

Former Member — Wed, 28 May 2008 09:36:48 GMT

you can do the following way.

report ztest.

data: begin of itab occurs 0,

accno type char10,

value type i,

end of itab.

data: it_acc like itab occurs 0 with header line,

it_acc1 like itab occurs 0 with header line.

Assume already value in itab.

select *

into table it_acc

from zacc

for all entries in table itab.

where accno eq itab-accno.

loop at it_acc.

collect it_acc to it_acc1.

endloop.

loop at itab.

collect itab to it_acc1.

endloop.

modify zacc from table it_acc1.

Reward points, if it is useful.

Regards

Raja.

Edited by: Ravindra Raja on May 28, 2008 5:38 PM

Re: Handling update and insert in a database table

Former Member — Wed, 28 May 2008 09:39:02 GMT

Hi ankit,

You can do it this way.

Select single * into lw_table from ( your table ) where acc = ( your account number which in this case is 10 ).

if sy-subrc eq 0.

lw_table-value = lw_table-value + 300.

Update table with lw_table.

else.

lw_table-acc_no = 20.

lw_table-value = 300.

insert table with lw_table.

endif.

I suggest for updating/inserting fields into table is done through a FM with update task.

Thanks.

Anand

Re: Handling update and insert in a database table

Former Member — Wed, 28 May 2008 09:39:08 GMT

HI ,

FIRST BEFORE MODIFYING THE DATA ,

DO COLLECT THE ITAB.

AND THEN MODIFY THE ITAB .

MODIFY ITAB .

YR PROBLEM WILL BE SOLVED.

IF HELPFUL DO REWARD POINTS

PRIYA

Re: Handling update and insert in a database table

Former Member — Wed, 28 May 2008 10:01:19 GMT

Hi,

The efficient solution to update a database table:

1. Find a FM which updates the database table.

2.If you are using an internal table to update database table.

use MODIFY dbtable FROM TABLE itab.

Modify statement serves your purpose of updating if the account no. is already existing or else creates a new row if it does not exist.

3.Use UPDATE statement , check below description from SAP help.

UPDATE dbtab FROM TABLE itab. or UPDATE (dbtabname) FROM TABLE itab.

Effect

Mass update of several lines in a database table.Here, the primary key for identifying the lines tobe updated and the values to be changed are taken from the lines of theinternal table itab.

The system field SY-DBCNT contains the number of updated lines,i.e. the number of lines in the internal table itab which havekey values corresponding to lines in the database table.

Note: Whenever you use open sql commands like 'Update' , use Commit statement to commit the dbase changes made.

Please reward points if helpful.

Re: Handling update and insert in a database table

Former Member — Wed, 28 May 2008 10:26:46 GMT

In case of modify no records are added but values are not updated.how to handle that