topic Open Sql! Update & Modify! in Application Development and Automation Discussions

Open Sql! Update & Modify!

Former Member — Thu, 07 Jun 2007 07:02:49 GMT

Hi!

Database Table Contains records,

1 Raj Chennai

2 Prem Mumbai

3 Akshay Bangalore

Itab Contains Records

1 Raj Chennai

2 Karan Mumbai

3 Rahul Kolkatta

4 Rani Delhi

5 Raja Noida

see, now i want to change as well as insert 4th & 5th record in database from internal table.

Can i use Modify i hope it ll modify changes & insert record

what about Update command have effect in the above issue

Pls tell me guys what will happen when we use both

Re: Open Sql! Update & Modify!

Former Member — Thu, 07 Jun 2007 07:05:53 GMT

hi,

Modify as u said inserts and will do the changes in the table.

Update just inserts the records into the table.

Message was edited by:

Roja Velagapudi

Re: Open Sql! Update & Modify!

Former Member — Thu, 07 Jun 2007 07:06:10 GMT

Hi,

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.

Regards

Sudheer

Re: Open Sql! Update & Modify!

Former Member — Thu, 07 Jun 2007 07:06:31 GMT

You can use

Insert or Modify commands

It will modify the DB table from the data of Int table

MODIFY

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

1. ... FROM wa

2. ... FROM TABLE itab

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.

INSERT

... { {VALUES wa}

| {FROM wa|{TABLE itab [ACCEPTING DUPLICATE KEYS]}} }.

1. ... {VALUES wa} | {FROM wa} ...

2. ... FROM TABLE itab [ACCEPTING DUPLICATE KEYS] ...

After FROM and VALUES, you can specify a non-table-type data object wa. After FROM, you can also specify an internal table itab. The contents of the row(s) to be inserted are taken from these data objects

UPDATE

... { {SET set_expression [WHERE sql_cond]}

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

1. ... SET set_expression [WHERE sql_cond]

2. ... FROM wa

3. ... FROM TABLE itab

The specifications in source define which rows and columns are changed. Either individual columns are changed using the addition SET or entire rows are overwritten using the addition FROM.

After FROM, either a non-table-type data object wa or an internal table itab can be specified. The content of these objects determines - on the one hand - which row(s) is/are changed, and - on the other hand - which values are used to overwrite the row(s).

Check out these related threads

Reward points for useful Answers

Regards

Anji

Re: Open Sql! Update & Modify!

Former Member — Thu, 07 Jun 2007 07:08:01 GMT

Hi Rahul,

Effect of Modify:

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 variable 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".

PS: Reward points if useful

Re: Open Sql! Update & Modify!

Former Member — Thu, 07 Jun 2007 07:08:40 GMT

"MODIFY dbtab FROM TABLE itab" will either update existing records or create new ones.

Regards,

Michael

Re: Open Sql! Update & Modify!

Former Member — Thu, 07 Jun 2007 07:16:35 GMT

Hi Michel!

What about update just update existing ones does it creates new one's from itab.

Re: Open Sql! Update & Modify!

Former Member — Thu, 07 Jun 2007 08:20:28 GMT

Hi rahul,

1. Update will only update.

2. Its better to use MODIFY statement.

Based upon the primary key found/not found

in database table,

it will automatially Update / INSERT

the record.

3. We can do like this.

Loop at ITAB.

Modify DatabaseTable From Itab.

ENDLOOP.

regards,

amit m.