topic Re: Update values for additional (new) keyfield in database table in Application Development and Automation Discussions

Update values for additional (new) keyfield in database table

Former Member — Wed, 16 Jan 2008 21:02:08 GMT

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

Re: Update values for additional (new) keyfield in database table

Former Member — Wed, 16 Jan 2008 21:06:46 GMT

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

Rob

Re: Update values for additional (new) keyfield in database table

Former Member — Wed, 16 Jan 2008 21:07:47 GMT

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

Regards,

Sudhir Atluru

Re: Update values for additional (new) keyfield in database table

Former Member — Wed, 16 Jan 2008 21:36:03 GMT

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

Re: Update values for additional (new) keyfield in database table

Former Member — Wed, 16 Jan 2008 21:41:06 GMT

Can you post your code?

Rob

Re: Update values for additional (new) keyfield in database table

Former Member — Wed, 16 Jan 2008 22:03:10 GMT

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.

endloop.

Re: Update values for additional (new) keyfield in database table

Former Member — Wed, 16 Jan 2008 22:21:24 GMT

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

Re: Update values for additional (new) keyfield in database table

Former Member — Wed, 16 Jan 2008 22:49:09 GMT

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

Re: Update values for additional (new) keyfield in database table

Former Member — Thu, 17 Jan 2008 16:49:54 GMT

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

Re: Update values for additional (new) keyfield in database table

Former Member — Thu, 17 Jan 2008 17:56:36 GMT

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

Re: Update values for additional (new) keyfield in database table

Former Member — Thu, 17 Jan 2008 18:06:24 GMT

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

Re: Update values for additional (new) keyfield in database table

Former Member — Thu, 17 Jan 2008 18:25:24 GMT

Oracle

Re: Update values for additional (new) keyfield in database table

Former Member — Thu, 17 Jan 2008 18:35:54 GMT

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

Re: Update values for additional (new) keyfield in database table

Former Member — Thu, 17 Jan 2008 18:41:21 GMT

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

Re: Update values for additional (new) keyfield in database table

Former Member — Thu, 17 Jan 2008 18:48:24 GMT

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

Re: Update values for additional (new) keyfield in database table

Former Member — Thu, 17 Jan 2008 18:59:19 GMT

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

Re: Update values for additional (new) keyfield in database table

Former Member — Thu, 17 Jan 2008 19:00:10 GMT

This question is unanswered till the UPDATE statement is succesfull.

Re: Update values for additional (new) keyfield in database table

Former Member — Thu, 17 Jan 2008 19:05:49 GMT

thanks

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

Re: Update values for additional (new) keyfield in database table

Former Member — Thu, 17 Jan 2008 20:38:21 GMT

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

Rob

Re: Update values for additional (new) keyfield in database table

Former Member — Thu, 17 Jan 2008 20:49:49 GMT

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