topic Update statement inside a Loop in Application Development and Automation Discussions

Update statement inside a Loop

Former Member — Fri, 02 Dec 2005 17:49:57 GMT

Hi,

Anybody knows how I can improve this code? It doesnt seem to be working right since i'm supposed to loop through it_zplan and use the value in it_zplan-seqnr to update a custom table zplan.

LOOP AT it_zplan.

UPDATE zplan

SET seqnr = it_zplan-seqnr

WHERE gjahr = it_zplan-gjahr AND

versn = it_zplan-versn AND

kostl = it_zplan-kostl AND

ctype = it_zplan-ctype AND

emgrp = it_zplan-emgrp AND

pernr = it_zplan-pernr.

ENDLOOP.

Thanks. Useful answer will be rewarded.

Re: Update statement inside a Loop

RichHeilman — Fri, 02 Dec 2005 17:53:42 GMT

Is ZPLAN and IT_ZPLAN have the same structure?

I never use the UPDATE statement, so I can't offer anything on that. I use modify.

report zrich_0001.



data: it_zplan type table of zplan.
data: wa_zplan like line of it_zplan.


loop at it_zplan into wa_zplan.
  modify zplan from wa_zplan.
endloop.

Regards,

Rich Heilman

Re: Update statement inside a Loop

Former Member — Fri, 02 Dec 2005 17:54:48 GMT

Hi,

We should not use UPDATE statement inside a loop. It won't work properly. Sometimes it may even cause a dump.

Try to use Modify instead of Update. Modify will update the database table and can be used within a Loop...EndLoop.

Pl. reward points if useful.

Re: Update statement inside a Loop

Former Member — Fri, 02 Dec 2005 17:59:39 GMT

Hi,

I tried this but the table was not updated since they dont have the same value in the key fields, seqnr is one of the key fields that will be updated inside the loop.

my table has these keys

1. pernr

2. seqnr

currently seqnr is empty

Re: Update statement inside a Loop

Former Member — Fri, 02 Dec 2005 18:03:18 GMT

Hi ricky,

Use the following code.

LOOP AT it_zplan.

modify zplan from it_zplan.

endloop.

you can directly use

modify zplan from table it_zplan.

Lokesh

Please reward appropriate points

Re: Update statement inside a Loop

RichHeilman — Fri, 02 Dec 2005 18:04:54 GMT

I think that when you change the key, a new record is created instead of modifing the existing. This happens when you are updating a field that is part of the key. Do you want to get rid of the old record when you update?

Regards,

Rich Heilman

Re: Update statement inside a Loop

Former Member — Fri, 02 Dec 2005 18:05:12 GMT

You cannot update key fields. You have to delete the record and add the new record with the updated key field.

Srinivas

Re: Update statement inside a Loop

RichHeilman — Fri, 02 Dec 2005 18:07:11 GMT

You can do something like this....




report zrich_0001.


data: it_zplan type table of zplan.
data: wa_zplan like line of it_zplan.

loop at it_zplan into wa_zplan.

* Modify will create the new record.
  modify zplan from wa_zplan.

* Now delete the old record.
  clear wa_zplan-seqnr.
  delete zplan from wa_zplan.

endloop.

Regards,

Rich Heilman

Re: Update statement inside a Loop

Former Member — Fri, 02 Dec 2005 18:08:44 GMT

Yes, this is the one i would like to happen:

old values:

PERNR SEQNR

0001 000

should be replaced with new values

PERNR SEQNR

0001 value in it_zplan-seqnr

Re: Update statement inside a Loop

RichHeilman — Fri, 02 Dec 2005 18:12:37 GMT

Following the coding above then. It works good for me.

Regards,

Rich Heilman

Re: Update statement inside a Loop

Former Member — Fri, 02 Dec 2005 18:16:29 GMT

try the below code.

data : it_zplan_mod type standard table of ZPLAN with header line.

LOOP AT it_zplan.

select single * into it_zplan_mod

where gjahr = it_zplan-gjahr AND

versn = it_zplan-versn AND

kostl = it_zplan-kostl AND

ctype = it_zplan-ctype AND

emgrp = it_zplan-emgrp AND

pernr = it_zplan-pernr.

it_zplan_mod-seqnr = it_zplan-seqnr.

modify it_zplan_mod.

delete from zplan

where gjahr = it_zplan-gjahr AND

versn = it_zplan-versn AND

kostl = it_zplan-kostl AND

ctype = it_zplan-ctype AND

emgrp = it_zplan-emgrp AND

pernr = it_zplan-pernr.

MODIFY zplan from it_zplan_mod.

ENDLOOP.

Pl. award appropriate points.

Re: Update statement inside a Loop

suresh_datti — Fri, 02 Dec 2005 18:16:52 GMT

Hi Ricky,

Which HR Table are you trying to Update? SEQNR can be updated on some tables eventhough it is part of the key.

Regards,

Suresh Datti

P.S. was I so dumb not see that it was a custom table?will blame it on Friday...

Message was edited by: Suresh Datti

Re: Update statement inside a Loop

Former Member — Fri, 02 Dec 2005 18:34:40 GMT

I used the solution given by Rich Heilman and it worked perfectly.

Thanks for all who replied.

Regards,

Ricky

Re: Update statement inside a Loop

Former Member — Fri, 02 Dec 2005 18:38:35 GMT

Hi Ricky,

As Srinivas said, you can never change the values of key fields of a record.(RDBMS concepts are at stake, if anything of that sort, ever happens!!!)

Though its good to know that, you were able to solve your problem, it points to a poor design of your custom table.

Regards,

Raj