05-28-2008 10:24 AM
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
05-28-2008 11:01 AM
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.
05-28-2008 10:27 AM
Hi,
if you work with modify,
the system makes a insert, if no record exists and an update, if one is there.
Regards
Nicole
05-28-2008 10:29 AM
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
05-28-2008 10:36 AM
Hi
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
05-28-2008 10:39 AM
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
05-28-2008 10:39 AM
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
05-28-2008 11:01 AM
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.
05-28-2008 11:26 AM
In case of modify no records are added but values are not updated.how to handle that