topic Re: updating database table from an internal table in Application Development and Automation Discussions

updating database table from an internal table

Former Member — Mon, 18 Dec 2006 00:17:46 GMT

hi! i am tryin to update ztable from an internal table(8 records). but i see that only one record gets updated in ztable. i used the followin code

loop at itab into wa.

modify z3pus from wa.

commit work.

if sy-subrc <>0.

write: / 'fail'.

else.

write: / 'success'.

endif.

endloop.

(used insert and update also not much luck.....)

i got output 'success' 8 times..........but i see only one record in ztable. what could possibly go wrong???????????????

Re: updating database table from an internal table

gopi_narendra — Mon, 18 Dec 2006 00:53:40 GMT

Hi Dilip,

when u write in the way u wrote it makes a performance issue as u r trying to update the database table in the loop. so each time it triggers the database and is not suggested.

instead u can update the database Z table just like this.

modify Z3PUS from table itab.

if sy-subrc = 0.

write : / 'Success'.

else.

write : / 'Fail.

endif.

Regards

- Gopi

Re: updating database table from an internal table

Former Member — Mon, 18 Dec 2006 02:26:09 GMT

You're checking sy-subrc after your COMMIT WORK statement, not the MODIFY statement.

COMMIT WORK always sets sy-subrc to 0, even if MODIFY set a different sy-subrc earlier on.

Hope that helped.

Re: updating database table from an internal table

Former Member — Mon, 18 Dec 2006 04:39:14 GMT

Hi Dilip ,

Did you check if the database table already contains the records havving the primary key same as that of your internal table.

Regards

Arun

Re: updating database table from an internal table

anversha_s — Mon, 18 Dec 2006 04:43:45 GMT

hi,

before that :- modify statemnt will modify a record if there is record having the same key like that of record in work area.

if there is no record in itab with key of work area record, it will insert a new record in itab.

Rgds

Anver

Re: updating database table from an internal table

Former Member — Mon, 18 Dec 2006 06:46:32 GMT

Hello,

try not to use MODIFY in a loop.

Regards,

Shehryar Dahar

Re: updating database table from an internal table

Former Member — Mon, 18 Dec 2006 06:56:57 GMT

Do not direcly modify from work are.

Atfirst select, if record presents then update.

*select record with

loop at ITAb.

select single * from ztable into wa

where a = ITAB-a and

b = ITAb-b.

*if there is, mark

if sy-dbcnt eq 1.

modify ztable from wa.

commit work.

endif. " modify the record

endloop.

Re: updating database table from an internal table

Former Member — Mon, 18 Dec 2006 09:16:23 GMT

Hi Dilip,

I can see problem due to Primary Key or Index of <b>z3pus</b> table. Please check <b>z3pus</b> table whether you have defined the Primary Key or Index. Write COMMIT WORK after your END...LOOP.

Regards,

Ramki.

Re: updating database table from an internal table

Former Member — Mon, 18 Dec 2006 16:00:58 GMT

Two things

You are getting sy-subrc eq zero because the commit work will set sy-subrc zero.

The problem you are facing related to updation is because either you are trying to insert the same data again and again which violates primary key/unique key.

Try with the statement

insert z3pus from wa.

Re: updating database table from an internal table

Former Member — Mon, 18 Dec 2006 16:31:04 GMT

hey gopi i tried what u said. i got output as success but still i see only one record.

Re: updating database table from an internal table

Former Member — Wed, 20 Dec 2006 06:36:45 GMT

You can try this .

loop at itab.

Ztab-field1 = itab-field1.

ztab-field2 = itab-field2.

modify ztab.

commit work.

endloop.

Rewards interms of points expected.

Message was edited by:

Manu Kapur