modify an database table

[Former Member]

hi ,

can we use modify command in database table .(not update command)

if yes means how to use it . give syntax ..

i want to modify the database table based on internal table i.e

how to do this .

Regards,

kumar

[Former Member]

Hi,

Modify DBTAB from table Itab.

It would modify the dbtable when the entries already exists or adds when there are no matching entries..

santhosh

[former_member632991]

Hi,

ya we can...

e.g.

Modify table vbap from table it_vbap.

for more help check the documanttaion for MODIFY statement.

Regards,

Sonika

[Former Member]

Hello Kumar,

Always use MODIFY command rather than using INSERT or UPDATE.

When u r using MODIFY command and u can able to update a record and insert a record inside the table.

MODIFY - Change a database table 

Variants: 

1. MODIFY dbtab. or 
MODIFY *dbtab. or 
MODIFY (dbtabname) ... .. 
2. MODIFY dbtab FROM TABLE itab. or 
MODIFY (dbtabname) FROM TABLE itab. 
3. 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. 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 
You cannot modify a line if there is already a line in the table with identical key field values in a UNIQUE index. 



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. 


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


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). 


Synchronization of simultanous accesses by several users to the same set of data cannot be guaranteed exclusively 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) ... . 


Additions: 

1. ... FROM wa 
2. ... CLIENT SPECIFIED 
See Short forms not allowed and * work areas not allowed. 


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 specify the name of the database table directly, the program must 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. 


This variant is not allowed in an ABAP Objects context. See VERSION addition not allowed.

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. 


Additional help 
Inserting or Changing Table Records 

If useful reward.

Vasanth

[Former Member]

hi

good

there is nothing like modify the database table,you can use the update statement here to update the required changes in the database table.

thanks

mrutyun^

[Former Member]

Hi,

Pl.refer below material.

Inserting or Changing Lines

To insert lines into a database table regardless of whether there is already a line in the table with the same primary key, use the following:

MODIFY <target> <lines>.

If the database table contains no line with the same primary key as the line to be inserted, MODIFY works like INSERT, that is, the line is added.

If the database already contains a line with the same primary key as the line to be inserted, MODIFY works like UPDATE, that is, the line is changed.

For performance reasons, you should use MODIFY only if you cannot distinguish between these two options in your ABAP program.

You can add or change one or more lines <lines> in a database table <target>. You can only insert or change lines in an ABAP Dictionary view if it only contains fields from one table, and its maintenance status is defined as Read and change. You may specify the database table <target> either statically or dynamically.

Specifying a Database Table

To specify the database table statically, enter the following for <target>:

MODIFY <dbtab> [CLIENT SPECIFIED] <lines>.

where <dbtab> is the name of a database table defined in the ABAP Dictionary.

To specify the database table dynamically, enter the following for <target>:

MODIFY (<name>) [CLIENT SPECIFIED] <lines>.

where the field <name> contains the name of a database table defined in the ABAP Dictionary.

You can use the CLIENT SPECIFIED addition to disable automatic client handling.

Inserting or Changing Single Lines

To insert or change a single line in a database table, use the following:

MODIFY <target> FROM <wa> .

The contents of the work area <wa> are written to the database table <dbtab>. The work area <wa> must be a data object with at least the same length and alignment as the line structure of the database table. The data is placed in the database table according to the line structure of the table, and regardless of the structure of the work area. It is a good idea to define the work area with reference to the structure of the database table.

If the database table does not already contain a line with the same primary key as specified in the work area, a new line is inserted. If the database table does already contain a line with the same primary key as specified in the work area, the existing line is overwritten. SY-SUBRC is always set to 0.

A shortened form of the above statement is:

MODIFY <dbtab>.

In this case, the contents of the table work area <dbtab> are inserted into the database table with the same name. You must declare this table work area using the TABLES statement. In this case, it is not possible to specify the name of the database table dynamically. Table work areas with the same name as the database table (necessary before Release 4.0) should no longer be used for the sake of clarity.

Inserting or Changing Several Lines

To insert or change several lines in a database table, use the following:

MODIFY <target> FROM TABLE <itab> .

Those lines of the internal table <itab> for which there is not already a line in the database table with the same primary key are inserted into the table. Those lines of the internal table <itab> for which there is already a line in the database table with the same primary key overwrite the existing line in the database table. The same rules apply to the line type of <itab> as to the work area <wa> described above.

SY-SUBRC is always set to 0. SY-DBCNT is set to the number of lines in the internal table.

Reward if useful.

Thanks,

USR