topic Re: How to update key field? in Application Development and Automation Discussions

How to update key field?

Former Member — Thu, 15 Apr 2021 09:35:28 GMT

Hello,

I have this problem:

In the table z_zxx I have key1, key2, key3, key4.

Key2 is a char field of length 12 but up to this point values of length 11 have been saved.

For example, values such as 123456789xx are saved instead of 0123456789xx.

I want to change key2 from 123456789xx to 0123456789xx

Modify z_zxx from wa_zxx creates a new entry with key 0123456789xx instead of modifying the existing one.

UPDATE z_zxx

SET key2 = wa_zxx-key2

WHERE key1 = wa_zxx-key1 AND

key2 = key2_old AND

key3 = wa_zxx-key3 AND

key4 = wa_zxx-key4.

How to solve this problem?

Re: How to update key field?

Former Member — Thu, 15 Apr 2021 09:36:53 GMT

I forgot to mention that Update statement does not work

Re: How to update key field?

MateuszAdamus — Thu, 15 Apr 2021 09:39:56 GMT

Hello tearm

You cannot. You need to create new entries with the new key values and delete the old records.

Edit: Actually it is possible, but with some constraints (thanks sandra.rossi for pointing that out):

The content of primary key fields can only be changed if the respective database table is not linked with a search help and if pool and cluster tables are not accessed. If these changes would create a row which would produce duplicate entries in the primary key or a unique secondary index of the database table, no rows are changed and sy-subrc is set to 4.

https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abapupdate_source.htm

Kind regards,
Mateusz

Re: How to update key field?

Former Member — Thu, 15 Apr 2021 10:35:33 GMT

Update should work on key fields as well as long as it creates no duplication.

Did you try to run the UPDATE statement you pasted?

Re: How to update key field?

Former Member — Thu, 15 Apr 2021 10:46:54 GMT

Yes, it returns sy-subrc = 4

Re: How to update key field?

Sandra_Rossi — Thu, 15 Apr 2021 12:45:13 GMT

Please, for future questions, select your code and press the "CODE" button to make it correctly colorized/indented, so that it's easier for us to analyze it. Thank you.

Re: How to update key field?

Sandra_Rossi — Thu, 15 Apr 2021 12:47:55 GMT

SQL permits to update the values of key columns, it's only preventing from having duplicate values of unique keys or other database constraints.

(but ABAP prevents from changing key fields of internal tables)

Re: How to update key field?

MateuszAdamus — Thu, 15 Apr 2021 13:04:08 GMT

Hi Sandra,

You're right. However, there seems to be an additional condition for this.

https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abapupdate_source.htm

I have update my answer accordingly.

Kind regards,

Mateusz

Re: How to update key field?

Sandra_Rossi — Thu, 15 Apr 2021 15:27:39 GMT

Thanks for the ABAP note.

I don't know what is the exact meaning about the search help, I did a quick ugly test with T002 which is linked to H_T002 at table level:

UPDATE t002 SET spras = '}' WHERE spras = 'D'.
WRITE sy-subrc.

In a Sybase 7.52 Developer Edition, the update was successful.

In a S/4HANA 7.52 system, there is a HANA database trigger which fails after ABAP:

Category               ABAP programming error
Runtime Errors         DBSQL_SQL_ERROR
Except.                CX_SY_OPEN_SQL_DB
ABAP Program           ZTEST
Application Component  Not assigned
Date and Time          15.04.2021 21:11:06

 Short Text
     SQL error "SQL code: 465" occurred while accessing table "T002".

 What happened?
     Database error text: "SQL message: Cannot execute trigger, was invalidated by
      object change: failure in revalidating check for update trigger
      (SAPXXX.T002_U_HADP_TRIG)"

I also did a test with T002T which has a search help at column level, but it was again successful.

Any idea what "can only be changed if the respective database table is not linked with a search help" means in the UPDATE context? 😉

Re: How to update key field?

MateuszAdamus — Thu, 15 Apr 2021 15:34:52 GMT

I understood it as "the table is not used in a search help", but I understand you tested it and this is not the case.

Kind regards,
Mateusz

Re: How to update key field?

Former Member — Fri, 16 Apr 2021 05:41:24 GMT

Update

Eventually i created new entries and deleted the old ones.

Thanks for the help.