How to update specific fields of a db table using MODIFY

[Former Member]

Hi all.

I understand that MODIFY allows us to insert a record into the database if the a record with the same key is not there. If a record is there, it would update that record.

However, when the record is there, could i update SPECIFIC fields of the record, instead of updating ALL fields of the record?

[Former Member]

Yes, you can with exception that you can not modify primary key values.

Here are the details about MODIFY db table command with examples

MODIFY - Change a database table

Variants:

MODIFY dbtab. or MODIFY *dbtab. or

MODIFY (dbtabname) ... .

MODIFY dbtab FROM TABLE itab. or MODIFY (dbtabname) FROM TABLE itab.

MODIFY dbtab VERSION vers. or MODIFY *dbtab VERSION vers.

Effect

Inserts new lines or updates existing lines in a database table (s. relational database). If a line with the specified primary key already exists, an UPDATE is executed. Otherwise, an INSERT is performed. You can specify the name of the database table either in the program itself in the form MODIFY dbtab ... or at runtime as the contents of the field dbtabname in the form MODIFY (dbtabname) ... . In both cases, the database table must be defined in the ABAP Dictionary. If the program contains the name of the database table, it must also have a corresponding TABLES statement. Normally, records are inserted or updated only in the current client. Data can only be inserted or updated using a view, if the view refers to a single table and was created in the ABAP Dictionary with the maintenance status "No restriction".

MODIFY belongs to the Open SQL command set.

When the statement has been executed, the system field SY-DBCNT contains the number of edited lines.

The Return code is set as follows:

SY-SUBRC = 0:

All lines were successfully inserted or updated.

SY-SUBRC = 4:

One or more lines could not be inserted or updated.

Notes

1. You cannot modify a line if there is already a line in the table with identical key field values in a UNIQUE index.

2. Automatic definition of INSERT and UPDATE is expensive. You should therefore use MODIFY only if you cannot define the INSERT and UPDATE cases yourself in the program.

3. Since the MODIFY statement does not perform authority checks, you have to program them yourself.

4. Adding or changing lines with the MODIFY command is only completed after a database commit (see LUW) has been performed. Before the database commit has been performed, any database changes can be reversed with a database rollback (see Programming transactions).

5. Synchronization of simultanous accesses by several users to the same set of data cannot be exclusively achieved with the lock mechanism of the database system. In several cases, you are recommended to use the SAP lock mechanism.

Variant 1

MODIFY dbtab. or

MODIFY *dbtab. or

MODIFY (dbtabname) ... .

Extras:

... FROM wa

... CLIENT SPECIFIED

See Cannot Use Short Forms and

Cannot Use *Work Areas.

Effect

Inserts a new line or updates an existing line in a database table. If you specify the name of the database table yourself, the primary key for identifying the line to be inserted or updated and the relevant values are taken from the table work area dbtab or *dbtab (see TABLES). If you declare the name of the database table explicitly, the program must also contain a corresponding TABLES statement. If the name of the database table is not determined until runtime, you need to use the addition ... FROM wa.

Example

Insert or change data of the customer Robinson in the current client:

TABLES SCUSTOM.

SCUSTOM-ID = '12400177'.

SCUSTOM-NAME = 'Robinson'.

SCUSTOM-POSTCODE = '69542'.

SCUSTOM-CITY = 'Heidelberg'.

SCUSTOM-CUSTTYPE = 'P'.

SCUSTOM-DISCOUNT = '003'.

SCUSTOM-TELEPHONE = '06201/44889'.

MODIFY SCUSTOM.

Addition 1

... FROM wa

Effect

The values for the line to be inserted or updated are not taken from the table work area dbtab, but from the explicitly specified work area wa. When doing this, the data is read from left to right according to the structure of the table work area dbtab (see TABLES). Since the structure of wa is not taken into account, the work area wa must be at least as wide (see DATA) as the table work area dbtab and the alignment of the work area wa must correspond to the alignment of the table work area. Otherwise, a runtime error occurs.

Note

If a work area is not explicitly specified, the values for the line to be inserted or updated are also taken from the table work area dbtab if the statement is in a FORM or FUNCTION where the table work area is stored in a formal parameter or local variable of the same name.

Addition 2

... CLIENT SPECIFIED

Effect

Switches off automatic client handling. This allows you to edit data across all clients even when dealing with client-specific tables. The client field is treated like a normal table field that can be programmed to accept values in the table work area dbtab or *dbtab where the line to be edited occurs.

The addition CLIENT SPECIFIED must be specified immediately after the name of the database table.

Variant 2

MODIFY dbtab FROM TABLE itab.or

MODIFY (dbtabname) FROM TABLE itab.

Addition:

... CLIENT SPECIFIED

Effect

Mass modify: Inserts new lines or updates existing lines of a database table. The primary keys for identifying the lines to be inserted or updated and the relevant values are taken from the internal table itab. The lines of the internal table itab must satisfy the same conditions as the work area wa in addition 1 to variant 1.

Note

If the internal table itab is empty, SY-SUBRC and SY-DBCNT are set to 0.

Addition

... CLIENT SPECIFIED

Effect

As for variant 1.

Variant 3

MODIFY dbtab VERSION vers. or

MODIFY *dbtab VERSION vers.

See Cannot Use the VERSION Addition.

Note

This variant is obsolete.

Effect

Inserts a new line or updates an existing line in a database table, the name of which is taken from the field vers at runtime. If no line exists with the specified primary key, an INSERT is executed. Otherwise, an UPDATE is performed. The database table must be defined in the ABAP/4 Dictionary and its name must conform to the naming conventions for R/2 ATAB tables. These stipulate that the name must begin with 'T' and may contain up to four further characters. The field vers must contain the table name without the leading 'T'. Only lines in the current client are inserted or updated. The line to be inserted is taken from the statically specified table work area dbtab or *dbtab.

SY-SUBRC is set to 0 if the line is successfully inserted or updated. SY-SUBRC <> 0 is not possible since any other result causes a runtime error.

Hope this helps.

ashish

[former_member223537]

HI,

<b>Existing record has values in DB:</b>

message_wa-sprsl = 'EN'.     " Primary Key
message_wa-arbgb = 'MYMSGCLASS'.   "Primary Key
message_wa-msgnr =  '200'. 
message_wa-text =  'OLD VALUE'.

<b>New values to be updated</b>

DATA message_wa TYPE t100. 

message_wa-sprsl = 'EN'.                          "Primary Key
message_wa-arbgb = 'MYMSGCLASS'. "Primary Key
message_wa-msgnr =  '100'.                      "Modified 
message_wa-text =  'This is the new Value'.  "Modified

MODIFY t100 FROM message_wa.

[Former Member]

hi,

use update command.

update mara set kwetr = 'fld_value' where mandt = sy-mandt and

other conditions.

[Former Member]

Hi,

yes you can update specific records by first selecting that record to work area and modifying the specific field in work area and then modifying DB entry from work area.

E.g. VBAK .

DATA: wa_vbak TYPE vbak.

say you have to modify KUNNR for sales order number 001.

SELECT * from vbak INTO wa_vbak WHERE vbeln = 001.

IF sy-subrc = 0.

wa_vbak-kunnr = 1000.

MODIFY vbak FROM wa_vbak.

ENDIF.

MODIFY statement modifies the value of database record with the values in the work area.

Hence you have to ensure you select all the fields into work area and modify the only field needed.

Regards,

Raghavendra

[Former Member]

Thanks, Raghavendra. That was the way I do it as well.

Thanks to the others for contributing.