04-15-2021 10:35 AM
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?
04-15-2021 10:39 AM
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
04-15-2021 10:36 AM
04-15-2021 10:39 AM
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
04-15-2021 1:47 PM
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)
04-15-2021 2:04 PM
Hi Sandra,
You're right. However, there seems to be an additional condition for this.
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
I have update my answer accordingly.
Kind regards,
Mateusz
04-15-2021 4:27 PM
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? 😉
04-15-2021 4:34 PM
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.
04-15-2021 11:35 AM
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?
04-15-2021 11:46 AM
04-15-2021 1:45 PM
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.
04-16-2021 6:41 AM
Update
Eventually i created new entries and deleted the old ones.
Thanks for the help.