topic Re: Updating table in Application Development and Automation Discussions

Updating table

Former Member — Tue, 19 Jun 2007 10:15:48 GMT

Hi,

i have a requirement where i have create custom transaction that will ask the user to enter the value of amount. and it will be stored in the one custom table.

my requirement is there should be only one entry in the table .if the user executes the transaction second time.it should override the value that is already exists in the table.

i am not getting the logic to keep the only value in the table . please help me.

regards

paveee

Re: Updating table

Former Member — Tue, 19 Jun 2007 10:17:35 GMT

hi..

by setting the primary key for a particular field in the table while creation you can avoid the duplication of entries..

thanks

Ashu

Re: Updating table

Former Member — Tue, 19 Jun 2007 10:18:26 GMT

Hi,

If u wantto over write a record, then ucan use MODIFY statement .

If u dont wantto over write a record, then u can go for INSERT tstament.

Revert back if any issues,

Reward with points if helpful ,

Regards,

Naveen

Re: Updating table

Former Member — Tue, 19 Jun 2007 10:19:07 GMT

hi ashu,

primary key of the table itself is amount.and mandt.

the table has got only three fields(custom table) mandt,amount,desc.

regards

pavee

Re: Updating table

Former Member — Tue, 19 Jun 2007 10:19:49 GMT

Direct database update

The following code can be used as a template to produce an ABAP which updates a particular database

table field

*& Report  ZUPDATE_PRPS                                                *
*&                                                                     *
*&---------------------------------------------------------------------*
*&                                                                     *
*&  Quick report to Update PRPS-FAKKZ database field                   *
*&---------------------------------------------------------------------*
Report  ZUPDATE_PRPS.

tables: prps.

parameter: p_wbs like prps-pspnr,
           p_value like prps-fakkz default 'X'.

data: wa_fakkz type prps-fakkz.


************************************************************************
*START-OF_SELECTION
start-of-selection.

call function 'CONVERSION_EXIT_ABPSP_INPUT'
     exporting
         input     = p_wbs
    importing
         output    = p_wbs
    exceptions
         not_found = 1
         others    = 2.

select single fakkz
  into wa_fakkz
  from prps
 where pspnr eq p_wbs.
if sy-subrc eq 0.
   update prps set fakkz = p_value where PSPNR eq p_wbs.
   if p_value is initial.
     message i999(za) with 'Billing element field has been unchecked'.
   else.
     message i999(za) with 'Billing element field has been checked'.
   endif.
else.
  message i999(za) with 'WBS element not found'.
endif.

reward points if it is usefull ...

Girish

Re: Updating table

Former Member — Tue, 19 Jun 2007 10:21:05 GMT

Hi deva,

i tried with modify but it appending the values .even insert will add the values

so it is not meeting the requirement.

regards

pavee

Re: Updating table

Former Member — Tue, 19 Jun 2007 10:21:54 GMT

Hi Praveen,

Naveen is correct. U should use the Modify statement. The modify statement works in the following way. If the record already exists, it overrides it with new content. If it does not exist, it creates a new entry. So this solves your purpose with a single statement.

Re: Updating table

Former Member — Tue, 19 Jun 2007 10:25:04 GMT

hi praveen.

then u can go for <b>MODIFY</b> statement since this statement will over write the existing record.

Thanks

Ashu

Re: Updating table

Former Member — Tue, 19 Jun 2007 10:26:42 GMT

Praveen,

you should have a logic which should check whether the Amount (input )already exists or not .

1. if it exists : use UPDATE or MODIFY stmt .

2. if the entry does not exist use INSERT stmt .

you have to use a where condition in 1 .

To check if the amount exists or not, you can have a select query and store the data in an int table .

Let me know if u face any problem .

~ Laxmi

Reward for helpful answers

Re: Updating table

Former Member — Tue, 19 Jun 2007 10:28:10 GMT

Hi ss,

MODULE USER_COMMAND_0100 INPUT.

save_ok = ok_code.

CLEAR ok_code.

CASE save_ok.

WHEN 'BUTTON'.

lw_ydamt-mandt = ydamt-mandt.

lw_ydamt-thdamt = ydamt-thdamt.

lw_ydamt-pordt = ydamt-pordt.

modify ydamt from lw_ydamt .

now i am using this code to update .

But if i executes the transaction second time and if i give different value. it is inserting new row.

Please help ...me...

points will be awarded.

regards

pavee

Re: Updating table

Former Member — Tue, 19 Jun 2007 11:20:45 GMT

Hi Praveen,

For ur sake, I myself made the program and tested it with the modify statement. It is working superb, the same way u want it. Just c below. I made a demo version by creating a ztable with three fields viz, mandt, numbr and name (mandt and numbr are my key fields). I created a screen in se51 with two fields ie numbr and name (gave dictionary names to the fields in Se41). Made a pushbutton 'UPDATE' with function code 'UPD' to update/insert the contents (as the case may be). Just see the code below.

REPORT ZSS_TEST1.

Tables : ZTAB1.

Data : itab like standard table of Ztab1,

wtab like ztab1.

call screen 1.

&----

*& Module USER_COMMAND_0001 INPUT

&----

text

----

MODULE USER_COMMAND_0001 INPUT.

CASE sy-ucomm.

WHEN 'UPD'.

lw_ydamt-mandt = ydamt-mandt.

wtab-numbr = ztab1-numbr.

wtab-name = ztab1-name.

modify ztab1 from wtab .

endcase.

ENDMODULE. " USER_COMMAND_0001 INPUT

After this I checked the contents in Se16 (just to be quick i omitted any exit or display button) but in the SE16 I could see that the program is inserting a record if it does not exist and modifying it if its exists.

Get back if u have any probs.

Re: Updating table

Former Member — Tue, 19 Jun 2007 11:51:05 GMT

Hi Praveen,

One more thing, while executing the transaction second time, you will have to see that you are giving the same values for key fields (for a particular record) and different values for non-key fields , if you dont want a new entry. If you check your transaction the second time by giving new values for the key fields, then obviously it is going to insert a new record rather than update anything.

Re: Updating table

Former Member — Tue, 19 Jun 2007 12:04:31 GMT

Hi SS,

thanks for ur response.

Yes. it is correct . the amount field is updating if the value is exists in the table and inserting if the user enters different value that is not exists in the table.

But i do not want second record in the table . eventhough the user enters different value for the second time.it should override that value also.

My requirement is there should be only record in the table.

Please help me...full points will be awarded.

regards

paveee.

Re: Updating table

Former Member — Tue, 19 Jun 2007 12:15:15 GMT

WRITE SELECT statement IF SY-SUBRC = 0. OR SY-DBCNT = 1 MODIFY THE ROW ELSE INSERT.

Re: Updating table

Former Member — Tue, 19 Jun 2007 12:18:29 GMT

Hi,

Try replacing the code like this.

MODULE USER_COMMAND_0100 INPUT.

save_ok = ok_code.

CLEAR ok_code.

CASE save_ok.

WHEN 'BUTTON'.

lw_ydamt-mandt = ydamt-mandt.

lw_ydamt-thdamt = ydamt-thdamt.

lw_ydamt-pordt = ydamt-pordt.

select single *

from ydamt

where thdamt = lw_thdamt.

if sy-subrc EQ 0.

modify ydamt from lw_ydamt .

else.

delete ydamt.

insert ydamt from lw_ydamt.

end if.

reward points if it was useful.

Regards,

Hema.

Re: Updating table

Former Member — Tue, 19 Jun 2007 12:34:24 GMT

Hi SS,

i got the solution ,

Ur code with some modification.

CASE sy-ucomm.

WHEN 'UPD'.

lw_ydamt-mandt = ydamt-mandt.

lw_ydamt-thdamt = ydamt-thdamt.

lw_ydamt-pordt = ydamt-pordt.

DELETE FROM ydamt.

MODIFY ydamt FROM lw_ydamt.

CLEAR sy-ucomm.

LEAVE PROGRAM

ENDCASE.

it is meeting my requiremennt..

thanks for ur asnwers.

regards

paveee...