topic update the database table in Application Development and Automation Discussions

update the database table

Former Member — Wed, 01 Aug 2007 17:14:12 GMT

Hi All,

i need to do direct update to the database table and the table has apprx 60,000 records . i am getting all records from database table to internal table and has to chnage the one of the field value and pass it to the database table . So i am looping the internal table and what is the best approach to update the database table is it to use UPDATE Pa0001 SET KOSTL = it_0001-KOSTL from table it_0001 each time in the loop to update each record and commit work which hits database for each record or use the statement UPDATE pa0001 from table it_0001 and if count = 1000 then COMMIT work in this way it will hit the database for every 1000 records . Appreciate your suggestions,

Thanks,

Latha.

Re: update the database table

RichHeilman — Wed, 01 Aug 2007 17:15:26 GMT

It is not suggested to do a direct database update. I would suggest you find another way to achieve your end result. Maybe using a standard funcition module or API.

Regards,

Rich Heilman

Re: update the database table

Former Member — Wed, 01 Aug 2007 17:19:18 GMT

Thanks Rich for quick response . Our team lead has accepted to do the direct table update as we are doing it one time and if we use other approach the changed by date field is changed and we dont want that to happen as it effects some of our interfaces so we are proceeding with direct table update . Please suggest which UPDATE method is appropriate .

Thanks,

Latha.

Re: update the database table

RichHeilman — Wed, 01 Aug 2007 17:22:12 GMT

There is no reason why you can't do it all at one time using the MODIFY statement. I have never used the UPDATE statement, I have only used the MODIFY statement.

MODIFY pa0001 from table it_pa0001.

Regards,

RIch Heilman

Re: update the database table

Former Member — Wed, 01 Aug 2007 17:30:17 GMT

HI,

see this code

<b>*-- Here only two hits to the database.</b>

tables : ztable.

data : itab like standard table of ztable.

select * from ztable into table itab.

if sy-subrc eq 0.

delete ztable from itab.

loop at itab.

do the changes to itab field.

endloop.

insert into ztable from table itab.

endif.

Thanks,

Mahesh

Re: update the database table

Former Member — Wed, 01 Aug 2007 17:39:18 GMT

You can use :

UPDATE PA0000 FROM TABLE T_PA0000.

I tried it and it worked..

Reward for useful answers

Regards

Pradeep

Re: update the database table

Former Member — Wed, 01 Aug 2007 18:00:40 GMT

My Code to update PA0006 is :



DATA:  t_pa0006 TYPE STANDARD TABLE OF pa0006 INITIAL SIZE 0,
       wa_pa0006 LIKE LINE OF t_pa0006,
       w_lin TYPE i.

FIELD-SYMBOLS:  <fs_pa0006> LIKE LINE OF t_pa0006.

SELECT * FROM pa0006 INTO TABLE t_pa0006 WHERE uname = 'PKHAROR'.

LOOP AT t_pa0006 ASSIGNING <fs_pa0006>.

  <fs_pa0006>-stras = 'My Street'.

ENDLOOP.

DESCRIBE TABLE t_pa0006 LINES w_lin.

UPDATE pa0006 FROM TABLE t_pa0006.
IF sy-subrc EQ 0.
  COMMIT WORK.
  WRITE:/ w_lin, ' Records have been updated..'.
ENDIF.

Reward points for useful answers

Regards

Pradeep

Regards

Pradeep