Modify Database table

[Former Member]

Hi all..

Can anyone tell me the ways to modify a database table directly in coding?

And which one is the best way in terms of perfomance.?

Thanks

[Former Member]

hi subhash,

MODIFY statement is used in codes for for modifying the db tables..

<b>Syntax</b>

MODIFY target FROM source.

<b>Effect</b>

The MODIFY statement inserts one or several lines specified in source in the database table specified in target, or overwrites existing lines.

<b>example:</b>

tables:zashtable.

data:itab like zashtable occurs 0 with header line.

itab-idno = 37.

itab-name = 'ramv'.

modify zashtable from itab.

Message was edited by: Ashok Kumar Prithiviraj

[Former Member]

Hi Subhash,

MODIFY dbtab - target

Syntax

... {dbtab|(dbtab_syntax)} [CLIENT SPECIFIED]

[CONNECTION {con|(con_syntax)}] ... .

Alternatives:

1. ... dbtab

2. ... (dbtab_syntax)

Extras:

1. ... CLIENT SPECIFIED

2. ... CONNECTION {con|(con_syntax)}

Effect:

The entries in target determine, statically or dynamically, which database table or which view is accessed, and control client handling.

Alternative 1

... dbtab

Effect:

For dbtab, a database table defined in the ABAP Dictionary or a view defined in the ABAP Dictionary can be specified.

Only views that refer to a single database table, and whose maintenance status in the ABAP Dictionary permits change access can be specified.

Alternative 2

... (dbtab_syntax)

Effect:

Instead of static specification, a bracketed data object dbtab_syntax can be specified, which must contain the name of the database table or the view when the statement is executed. A character-type data object can be specified for the data object dbtab_syntax. The syntax in dbtab_syntax, as in the AAP Editor, is not case-sensitive.

Note:

Before release 6.10, it was only possible to specify a flat character-type data object for dbtab_syntax, which had to contain the name of the database table or the view in capital letters.

Addition 1

... CLIENT SPECIFIED

Effect:

This addition deactivates the automatic client handling of Open SQL. If the addition CLIENT SPECIFIED is used, the client identifier specified in source is taken into account. Without the addition CLIENT SPECIFIED, the ABAP runtime environment does not transfer the client identifier specified in source to the database system, but transfers the identifier of the current client instead.

Note:

As of release 6.10, the client identifer specified in source remains unchanged during automatic client handling. Before release 6.10, it was overwritten with the current client identifier.

Addition 2

... CONNECTION {con|(con_syntax)}

Note

This addition is for internal use only.

It cannot be used in application programs

Effect

The Open SQL command is not executed on the standard database but on the specified secondary database connection . The database connection can be specified statically with con or dynamically as the content of con_syntax, where the field con_syntax must belong to the type c or string. The database connection must be specified with a name that is in column CON_NAME in table DBCON.

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

Note

If the Open SQL command is to be possible on a secondary database connection, the table definitions in the connection must be the same as those on the standard database.

MODIFY dbtab - source

Syntax

... FROM { {wa} | {TABLE itab} }.

Alternatives:

1. ... FROM wa

2. ... FROM TABLE itab

Effect

A wa data object that is not table-type or an itab internal table can be specified after FROM. On the one hand the content of the data objects determines whether the line(s) are inserted or changed, and on the other hand, which values are inserted or used for changes.

Alternative 1

... FROM wa

Effect

When a wa work area that is not table-type is specified, which meets the requirements for use in Open SQL statements, a line is searched for in the database table that has the same content in the primary key as the corresponding beginning part of the work area.

If such a line is not found, a new line is inserted according to the same rules as for the INSERT statement.

If such a line is found, this line is overwritten according to the same rules as for the UPDATE statement.

If the change would lead to a double entry in a unique secondary index, then it is not executed and sy-subrc is set to 4.

Notes

The wa work area should always be declared with reference to the database table or the view in the ABAP Dictionary.

If the the database table or view is specified statically, then you the specification of the work area using FROM wa can be ommitted outside of classes if a dbtab table work area is declared for the corresponding database table or for the view using the TABLES statement. The system enhances the MODIFY statement implicitly with the FROM dbtab addition.

Example

Create or change a message in database table T100. If there is no message with the number 100 in the MYMSGCLASS message class in English, it will be created. Otherwise only the text is changed.

DATA message_wa TYPE t100.

message_wa-sprsl = 'EN'.

message_wa-arbgb = 'MYMSGCLASS'.

message_wa-msgnr = '100'.

message_wa-text = 'Some new message ...'.

MODIFY t100 FROM message_wa.

Alternative 2

... FROM TABLE itab

Effect

If an itab internal table is specified, the system processes all lines in the internal table according to the rules for the wa work area. The line type of the internal table has to meet the requirements for use in Open SQL statements.

If the change to a line in the internal table would lead to a double entry in a unique secondary index, the corresponding line is not inserted and sy-subrc is set to 4. If the internal table is empty, sy-subrc is set to 0. The sy-dbcnt system field is always set to the number of lines that were actually processed.

Hope it helps...

But I would suggest you not to modify the SAP Standard tables directly, rather use a Standard FM to modify the tables....

Lokesh

[Former Member]

There are different ways to modify a table.

Take a look at transaction abapterm in combination with the keywords: modify, insert, delete.

There you´ll find different examples depending on whether you want to change one record or many records in your database table.

[Former Member]

hi subhash,

1. are u talking about the database table

structure or records?

2. if u are talking about structure,

then its not recommended to do thru abap coding.

( i don't know if any FM is provided for that)

3. use se11, se14 to do such things.

4. Since these are one times changes,

performance don't matter.

(in both cases, it will be same only)

regards,

amit m.

[Former Member]

If you want to modify your database table structure,

you must use Native SQL statements...

ALTER TABLE :tabl ...

[Former Member]

Hi Amit and all..

I am talking specifically of records of the table.

I know about the MODIFY commmand but can you tell me the most effective way of modifying the database table?

Can I do it without using any internal tables?

Thanks

[Former Member]

Hi Subash,

Hope the best method would ne to populate into an ITAB and use..

MODIFY <table> from TABLE itab.

regards

satesh

[Former Member]

hi Subash,

use MODIFY <b><TARGET Table></b> from <b><SOURCE Table></b> here both source and target tables are internal tables...

hope this helps,

Santosh P

[Former Member]

The MODIFY statement changes existing lines and inserts lines which do not exist.

Sflight-carrid = ‘MN’.

Sflight-connid = ‘454’.

UPDATE SFLIGHT where CARRID = ‘LH’.

Or

TABLES SFLIGHT.

UPDATE SFLIGHT SET PRICE = 1100

WHERE CARRID = ‘LH’.

Here price of sflight will get updated with new price 1100.

[Former Member]

HI

THE FOLLOWING LINK GIVES THE DIFFERENT METHODS TO MODIFY A DATABASE TABLE

<a href="http://64.233.179.104/search?q=cache:-YkpCM_BNVMJ:www.geocities.com/SiliconValley/Campus/6345/modify_d.htmMODIFYTABLEINABAP&hl=en&gl=in&ct=clnk&cd=7">TABLE MODIFICATION</a>

REGARDS

ANOOP

[Former Member]

hi subash ,

u need to modify the database table , since it is modify

1. do u want to change a particular data for a criteria in the db?

or

2. do u want to insert some data ?

cause modify can do both the instances .

IF THE HIT FAILS FOR MODIFY THEN A RECORD WILL BE INSERTED into the DB.

if the record is there for a particular criteria and u want to change something

then select the particular list and modify the change onto ur db table ,

SELECT F1 F2 F3 F4

WHERE COND1

AND COND2

AND COND3 ETC

IF SY-SUBRC = 0.

MODIFY TABLE XXXX USING ITAB OR WA_ITAB.

ENDIF.

OR LOOP AT ITAB .

MODIFY TABLE XXXX USING ITAB.

ENDLOOP.

Regarding performance it will depend on the number of records that u want to modify.

regards,

vijay.

[hymavathi_oruganti]

MODIFY DBTAB/ (DBTAB) FROM WA/ITAB.

(DBTAB) -

---

represents dbtab at runtime

Effect

The MODIFY statement inserts one or several lines specified in source in the database table specified in target, or overwrites existing lines.

System fields

The MODIFY statement sets the values of the sy-subrc and sy-dbcnt system fields.

[Former Member]

Hi,

Using Modify statement in database table is a costly statement. If the entry is not present in the table , then insertion of the record otherwise updation of the particular record will be made.

Instead , use insert or update statement should be use

depending upon the logic you needed.

Regards,

M.Saravanan

[Former Member]

Hi, I make a loop..in a table like..

LOOP AT GT_LIST_2 into list_2.

.....

......

MODIFY gt_LIST_2 FROM LIST_2.

ENDLOOP.

The definition of a table I made like:

DATA: BEGIN OF LIST_2 OCCURS 0,

EBELN LIKE EKBE-EBELN,

EBELP LIKE EKBE-EBELP,

GJAHR LIKE EKBE-GJAHR,

BELNR LIKE EKBE-BELNR,

BEWTP LIKE EKBE-BEWTP,

BUDAT LIKE EKBE-BUDAT,

MENGE LIKE EKBE-MENGE,

WRBTR LIKE EKBE-WRBTR,

WAERS LIKE EKBE-WAERS,

WERKS LIKE EKBE-WERKS,

SHKZG LIKE EKBE-SHKZG,

DMBTR LIKE EKBE-DMBTR,

END OF LIST_2.

DATA gt_list_2 LIKE SORTED TABLE

OF list_2

WITH NON-UNIQUE DEFAULT KEY.

But... When the program arrive to MODIFY has a mistake... I don´t know exactly how I can modify the sorted table, can anybody tell me how I can do this?

Thank you.