Update values for additional (new) keyfield in database table

[Former Member]

Hi All,

Here in our firm we have a Z table with data in it. As part of the new requirement I had to add a new key field in it.

Now I have to maintain values for these key fields in the already existing data.

I cannot maintain it though SM30 as the maintenance view grays that field in SM30.

Is there any way of updating this new key field data in database.

Thanks,

Kajal

[Former Member]

You should be able to do this through an ABAP update program. It may depend on your database though, to change key fields.

Rob

[Former Member]

Hi Rob,

I have tried updating it through a custom program but it is not updating in database.

Could you tell me how it depends on database ? If I know I would make the necessary changes.

Thanks,

Kajal

[Former Member]

Can you post your code?

Rob

[Former Member]

Hi Rob,

This is just a test code I was writing. Just to see if it updates a single line or not.

itab_gso-WORK_COUNTRY = 'US'.

itab_gso-GLB_ORG = 'GCA'.

itab_gso-GLB_SORG = 'CA001'.

itab_gso-HC_GROUP = 'CA00001'.

itab_gso-LEVEL3_GROUP = 'CA00001'.

append itab_gso.

clear itab_gso.

loop at itab_gso.

select single * from ZTHRPA_GSO_HC where WORK_COUNTRY = itab_gso-work_country and

GLB_ORG = itab_gso-glb_org and GLB_SORG = itab_gso-glb_sorg and

HC_GROUP = itab_gso-hc_group.

if sy-subrc eq 0.

update ZTHRPA_GSO_HC from itab_gso.

if sy-subrc ne 0.

write:/ 'error'.

else.

write:/ ' lines updated'.

write:/ itab_gso-work_country,

itab_gso-glb_org,

itab_gso-glb_sorg,

itab_gso-level3_group,

itab_gso-hc_group.

endif.

endif.

endloop.

[Former Member]

Hello Kajal,

an update will fail, as you want to change the keys of your records (the same in SM30 -> the new field is blank and cannot be modified).

Therefore you've two options:

SM30 -> mark all entries -> copy -> add value to your new field

After copying the records, you can select all records with the empty field in the key and delete them.

depending on the no. of entries, a report will be the better option:

Select all existing entries

Loop at all selected records

-> add a value to your new key field

-> INSERT into Z-table

Endloop

delete all entries where your new field is initial

Best regards

Stephan

[Former Member]

Hi Stephan,

Looks like this is the only way of updating the new field data in the table.

But I wanted to confirm on this. Thanks a lot of both the ideas.

Kajal

[Former Member]

Stephan - this is incorrect (it least in a DB2 and probably an Oracle environment). You can change key fields of a table. I made the same mistake a few months ago and someone pointed it out to me. When I actuallly tried changing a key field, I was surprised to find that it worked.

Rob

[Former Member]

Hi Rob,

So you think I can update the new key field using the ABAP code in database.

Did you see my code which I posted.

I am getting error. What maybe the prob ?

Thanks,

Kajal

[Former Member]

Well, what's the error.

I tried chaning a key field before I posted, so I know that in DB2 it works. What database are you on?

Rob

[Former Member]

Oracle

[Former Member]

There were two questions there. the other is - what error are you getting?

Is it sy-subrc = '4' ? It looks like your program is trying to create duplicate keys which is not allowed.

Rob

[Former Member]

I am getting sy-subrc = 4. But I don't think there are any duplicates created when I am updating.

I tried to update a single line first with the new field.

Kajal

[Former Member]

From F1 on UPDATE:

The Return Code is set as follows: 

SY-SUBRC = 0: 
The specified line has been updated. 
SY-SUBRC = 4: 
The system could not update any line in the table, since there is no line with the specified primary key. 

I see that you've also marked this question as answered, you might consider taking that off and assigning points to the people who have helped.

Rob

[Former Member]

Hi Rob,

I know what sy-subrc stands for in an update statement. Thanks anyway.

I marked this question answered as I thought Stephan gave the way to update the database...which I

tried and entries were updated.

But I will have to try this update ABAP code ...untill its gets updated I don't think it has solved the issue.

Thanks,

Kajal

[Former Member]

thanks

Edited by: Kajal Srivastava on Jan 17, 2008 8:07 PM

[Former Member]

Do any of the records get updated? Have you stepped through this in debugging?

Rob

[Former Member]

Hello Rob,

thank you for this information. Up to now, I thougth, that it is impossible to update the primary key. I've checked it in our system and it works. But it seems, that you have to use the "update set" to update a field of the primary key - am I correct?

@Kajal,

I've created a table:

Field Key Data element

MANDT X MANDT

MATNR X MATNR

MAKTX MAKTX

Then I've tried the following coding:

data: wa_test type zithtest.

select single * from zithtest into wa_test.

move: 'DE' to wa_test-spras.

update zithtest from wa_test.

write: sy-subrc.

select * from zithtest into wa_test.

write: / wa_test.

endselect.

result: sy-subrc = 4 and no update of the primary key

Then I've tried the following:

data: wa_test type zithtest.

select single * from zithtest into wa_test.

move: 'DE' to wa_test-spras.

update zithtest set spras = wa_test-spras

where matnr eq wa_test-matnr.

write: sy-subrc.

select * from zithtest into wa_test.

write: / wa_test.

endselect.

Result: sy-subrc = 0 and the primary key has been updated.

By the way - reward points to Rob, as he has given the correct solution.

Best regards

Stephan

Edited by: Stephan Theis on Jan 17, 2008 9:50 PM

[Former Member]

Stephan - unfortunately, when it happened to me I had argued longer and harder that it was impossible

Rob

[Former Member]

Hi Rob,

it was easy to test. Otherwise, I would have argued longer too.

Thanks again for a very useful hint!

Best regards

Stephan

[Former Member]

But I was soooo sure I was right, I didn't think I needed to test.

Just about always a mistake.

Rob

[Former Member]

You can write a custom program with an Update query on the table.

Regards,

Sudhir Atluru

[Former Member]

This question is unanswered till the UPDATE statement is succesfull.