Update statement inside a Loop

[Former Member]

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.

[RichHeilman]

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

[Former Member]

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

[RichHeilman]

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

[RichHeilman]

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

[Former Member]

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

[RichHeilman]

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

Regards,

Rich Heilman

[suresh_datti]

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

[Former Member]

Hi

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

Thanks for all who replied.

Regards,

Ricky

[Former Member]

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.

[Former Member]

Hi ricky,

Use the following code.

LOOP AT it_zplan.

modify zplan from it_zplan.

endloop.

OR

you can directly use

modify zplan from table it_zplan.

Lokesh

Please reward appropriate points

[Former Member]

<b><u>You cannot update key fields</b></u>. You have to delete the record and add the new record with the updated key field.

Srinivas

[Former Member]

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.

[Former Member]

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!!!)

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

Regards,

Raj