topic Re: Deletion from multiple tables...? in Application Development and Automation Discussions

Deletion from multiple tables...?

Former Member — Wed, 21 Dec 2005 10:44:53 GMT

I have 1 Master table and 10 child tables. I need to delete entries from these tables. I want to be sure that if there is some problem in the database then I should be able to roll back my deletion. I cannot check for sy-subrc after every delete as there may be some child tables which will not have corresponding entries. Any ideas how we can achieve the optimal design for the same, using commit work. The no of entries which needs to be deleted is huge around 5-6 millions of records need to be deleted. Should we use enqueue deque fn modules?

Loop at itab,

Delete child table1 where key = itab-key.

Delete child table2 where key = itab-key.

Delete child table 10 where key = itab-key.

Delete master table where key = itab-key.

Endloop.

Regards,

Ankur Bhandari

Re: Deletion from multiple tables...?

Former Member — Wed, 21 Dec 2005 10:50:59 GMT

Hi Ankur,

1. Exactly for such purposes,

there is the concept of UPDATE MODULE (Update FM)

2. Create a new FM.

In the atriburtes,

mark it as UPDATE MODULE (Start Immed)

3. Bunch all your sqls in this FM

4. When calling the FM

use the syntax

CALL FUNCTION func IN UPDATE TASK

I hope it helps.

Regards,

Amit M.

Re: Deletion from multiple tables...?

Former Member — Wed, 21 Dec 2005 10:53:32 GMT

Hi Ankur,

One way of achieveing this is,

While looping on itab, select the record from table1 into itab1. And then, use statement,

<b>DELETE table1 FROM TABLE itab1.</b> , To delete the entries from table1.

Similarly, for all tables(child), and at the last use same procedure to delete master table record.

Dont forget to refresh these internal table contents at each loop pass.

Regards,

Raj

Re: Deletion from multiple tables...?

Former Member — Wed, 21 Dec 2005 10:56:21 GMT

Any sample code would be helpful.How will this ensure my objective.

Ankur Bhandari

Re: Deletion from multiple tables...?

Former Member — Wed, 21 Dec 2005 11:16:28 GMT

Where do I need to do a commit in this case ?

Will it still work if there are no entries in the child table.In that scenario we dont require a roll back?

Please clarify in detail.

Ankur bhandari

Re: Deletion from multiple tables...?

Former Member — Wed, 21 Dec 2005 11:35:55 GMT

Hi again,

1 Where do I need to do a commit in this case ?

in the FM itself.

write this statement of commit

as the last statement.

OR U CAN USE THIS STATEMENT

IN UR CALLING PROGRAM ALSO.

2. Will it still work if there are no entries in the child table.

If there are not entries in child table,

then no need to worry!

3. In that scenario we dont require a roll back?

Rollback not required.

If any error occurs in betwee,

the system will take care of rollback by itself.

(THE Update FM concept is designed for this purpose only)

I hope it helps.

regards,

amit m.

Message was edited by: Amit Mittal

Re: Deletion from multiple tables...?

Former Member — Wed, 21 Dec 2005 11:57:24 GMT

Is there a sample code which can be used as a guideline for the same.

What will be the exporting importing parameters for this function module.How will the exceptions be handled ?

Ankur

Re: Deletion from multiple tables...?

Former Member — Wed, 21 Dec 2005 12:02:40 GMT

Hi again,

1. What will be the exporting importing parameters for this function m

The IMPORTING parameter

will be just the PRIMARY KEY value

of the Main Table

Using this key in your sqls

u can delte the tables.

2 How will the exceptions be handled ?

Since it is Update FM,

it is not executed immediately but later.

so i don't think exceptiosn will be of any help.

(i may be wrong here)

regards,

amit m.

Re: Deletion from multiple tables...?

Former Member — Wed, 21 Dec 2005 12:46:35 GMT

Hi please refer the following program which does the same functionality as ur requirement. COM_HIERARCHY_DELETE_ALL.

Just use commit work after the last delete statement in ur code. It will do the needed.

Satish

Re: Deletion from multiple tables...?

Former Member — Mon, 09 Jan 2006 11:43:11 GMT

Suppose I implement this functionality, How do I find out the total no of records which have been deleted. Earlier I could use sy-dbcnt after every delete statement.

*********************************************************

Loop at itab.

call function zfum in update task.

commit work.

endloop.

function module zfum.

delete child table 1

delete child table 2.

delete master table.

Re: Deletion from multiple tables...?

Former Member — Mon, 09 Jan 2006 12:22:10 GMT

Hi Ankur,

1. Since it is a update FM,

we cannot return / export back any parameter.

2. Hence, there is no direct way

to achieve what u require.

regards,

amit m.