Using MODIFY for a database table

[Former Member]

Hi,

I have created one custom table which I am updating through a custom program.

In the custome program, I am using MODIFY dbtab FROM TABLE itab.

I have executed the program and say 600 entries are added into dbtab.

Now, I have deleted 100 entries in the itab and I am running the report program again.

But thhose 100 entries are not getting deleted.

My dbtab still shows 600 entries.

So, Is the MODIFY statement only insert/update the table and does not delete the table?

Kindly provide a solution.

Thanks & Regards,

Mani

[Former Member]

Hi,

As you said the modify statement will only insert/update the rows as per the internal table values. It wont delete the database records which are not available in itab using MODIFY statement.

Instead of deleting the values in the internal table use delete statement by transferring the required records into another internal table itab1

DELETE Ztable FROM table itab1.

Regards,

Vikranth

[Former Member]

Hi

The modify can only insert/update your DB table . If you want to delete rows from DB table you have " DELETE" Command.

if you want to delete some rows use like this .

delete from ztest where ort01 = 'hyd'.

or

want to delete all the rows use this.

delete from ztest.

Regards,

Vijay

[Former Member]

Hi,

uses of modify statement

MODIFY:

it will work as both insert and update depending on condition. when a record is found then modify works like update and saves that changes to internal table with new record.

if it didnt found any record then it appends a record at the end of internal table with given data.

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.

regards,

Archana

[Former Member]

Hi Mani,

The MODIFY statement Updates/inserts one or several records in target database table based on source internal table.

As per your logic, you have deleted 100 entries, so your source internal table will have 500 entries.

Modify statement will work based on the 500 entries which is in your internal table.

[Former Member]

Hi,

Modify statement perform the combined role of UPDATE + INSERT statement.

It means if the entry doesnt exist in DB table it will create a new entry (INSERT) else it will update the exixting entry (MODIFY).

Since you have deleted 100 entries from your internal table so modify statement will work only for the 500 entries which are in internal table and it will not make any impact on the entries which are not in internal table.

To delete entries from DB table you can use delete statement.

Delete ZTAB from table ITAB.

the above statement delete all the entries which are present in internal table from db table.

[Former Member]

Hello Mani,

That's right! Modify statement will only insert/update statement in database and not delete them.

you need to use delete syntax for deleting data from databaes.

Thanks,

Augustin.

[Former Member]

Hi All,

Thankf for all your replies.

But I dont want to delete the entries which are in the itab.

Rather, i want to delete the entries which are not in the itab and which are in the dbtab.

Regards,

Mani

[Former Member]

Hi mani ,

If you want to delete entries which are in db table

use the fallowing statement

delete from ztab where ort01 = 'hyd'.

it will delete the entries from db tab(ztab) with that given condition.

Regards,

Vijay

[Former Member]

Hi,

As i mentioned move all the records from the internal table itab to another internal table itab1 which you want to delete. i,e those 100 records.

Now itab1 will hold those 100 records. Now use this syntax.

DELETE Ztable from TABLE itab1.

Regards,

Vikranth

[Former Member]

But the problem is I dont know which entries are need to be deleted.

This program needs to be run everyday in background. The data that comes from the internal table might notbe teh same always. Today, few of the entries may get deleted and tomorrow, some new entries may come. etc etc.

So, I think, we can loop at the db table and then READ the itab.. If that entry is not present, we can delete the entry from db table.

But in production, it will have more than 3 lakh entries and it will definitely affect the performance.

Is tehre any other way with which we can achieve this without a loop?

-Mani

[Former Member]

Hi,

It is true that deleting the ztable in a loop will affect the performance badly.

One performance effective way can be something like this.

Now that you have the internal table with the records already present, write this select

data: itab1 type standard table of Ztable. 

if not itab[] is initial.
SELECT * 
from Ztable 
into ITAB1 
for all entries in ITAB
where NOT field1 = itab-field1.         " Here field1 must be the primary key
endif.

Now itab1 will hold all the records that are not available in itab but available in the dbtable.

Now you can use

DELETE Ztable from TABLE ITAB1.

This is performance effective even for large databases.

Regards,

Vikranth

[SuhaSaha]

>

> Hi All,
> Thankf for all your replies.
> But I dont want to delete the entries which are in the itab.
> Rather, i want to delete the entries which are not in the itab and which are in the dbtab.
> 
> Regards,
> Mani

Hello Mani,

Why are you complicating this simple requirement?

You want to keep your table updated with the data in the internal table & flush out all the other data. If yes, read on:

1. For every run of the program, delete all the entries from the DB table.

DELETE FROM <Z_TABLE> CLIENT SPECIFIED WHERE MANDT = SY-MANDT

2. Update the table with the entries from your internal table.

MODIFY dbtab FROM TABLE itab.

Hope i am clear.

BR,

Suhas

[Former Member]

Hi Suhas,

I considered the same solution you have proposed.'

But do you think it is a good practise to delete more than 3 lakh entries in the database every day and load the new entries back.

Hi Vikranth,

I think your solution will work fine for me.

I will try that and let you know.

Thanks a ton for everyone who replied.

Regards,

Mani

[SuhaSaha]

Hello,

And what good practice is it to SELECT data from the table which are not in the internal table. (This will anyways scan your 3 lacs records as you have mentioned NOT in the WHERE condition) & then DELETE the data.

Common sense points out that you got to access the DB 2 times. Then you have to upload again to the DB, so in total you have to access the DB 3 times.

But when you DELETE all the records & then upload the data it is 2 times.

If you have any arguments in favour of Vik's solution do let me know. Eager to here them.

BR,

Suhas

[Former Member]

Hello Suhas,

As per my solution there are only 2 hits to the database and not 3 as u mentioned.

1------> When selecting the records not in itab.

2------>Deleting the Ztable as per the records collected in the itab above.

There is nothing required to upload here again as only the non available data are deleted. Hope understanding this doesnot require any common sense.

Infact there is no real performance difference with what you have suggested and what i suggested. Its upto the choice of the OP now to decide what has to be used.

Regards,

Vikranth

[SuhaSaha]

Hello Vik,

There is nothing required to upload here again as only the non available data are deleted. Hope understanding this doesnot require any common sense. 

Great, so you donot have to upload the data !!!

For e.g., Today your table has 3 entries 1, 2, 3. But your internal table has only 1 & 3. So you delete 2. Right

Tommorow you DB table has entries 1 & 3 but your internal table has got 2 & 4. So you delete 1 & 3 and DONOT UPLOAD 2 & 4.

What logic is this? Is it correct?

I dont think it is ?

BR,

Suhas

[Former Member]

Hello suhas,

That really depends on the source of the base internal table. It is not mentioned if the internal table data is extracted only within the database table or it can hold data outside of the Dbtable. In the question the OP has mentioned that he deleted 100 entries from the already available 600 entries and that he wanted to delete those 100 entries. So its assumed that he wants to delete the data which are already available in the DBtable and not uploading anything new here.

Regards,

Vikranth

[SuhaSaha]

>

 So its assumed that he wants to delete the data which are already available in the DBtable and not uploading anything new here. 

Hello Vik,

I think this statement nullifies all your assumptions:

This program needs to be run everyday in background. The data that comes from the internal table might notbe teh same always. Today, few of the entries may get deleted and tomorrow, some new entries may come. etc etc.

Need i say anything more

BR,

Suhas

[Former Member]

Hello suhas,

That does not completely nullify my assumption.

The new entries mentioned are with respect to the internal table and not the database table. So it can be assumed that the new data is something which is already available in the Dbtable and new to the internal table.

Also the OP must be fed up by now with our discussions with constant email alerts. Lets leave the choice to the OP.

Regards,

Vikranth