topic Re: DELETE FROM dbtab with functionality of MODIFY ? in Application Development and Automation Discussions

DELETE FROM dbtab with functionality of MODIFY ?

former_member5350 — Mon, 17 Mar 2008 09:30:39 GMT

hi,

i have two tables that i want to keep synchronized, that means if in left table something is inserted or modified, this also has to be done in right table - i have done this with MODIFY statement and it works.

now i have to deal with entries that were deleted in left table and here is the problem:

i do loop right table and i check, wheter left table has this entry too or not. if it doesnt have,

then i delete the entry from right internal table and do MODIFY dbtab from internaltable, but nothing happens on db table.

i read that MODIFY works like UPDATE and INSERT , what i need is UPDATE and DELETE...does this work ?

is there an alternative to do it ?

i will give points immediately, as this is very important for me to be solved!

Re: DELETE FROM dbtab with functionality of MODIFY ?

Former Member — Mon, 17 Mar 2008 09:37:03 GMT

not possible, you need to set a delete Statement on you second table as well.

Re: DELETE FROM dbtab with functionality of MODIFY ?

Former Member — Mon, 17 Mar 2008 09:37:41 GMT

I would do it like this.

1. Select all record from table B which are not in table A.


SELECT B.key_fields A.some_field_that_is_always_filled 
  INTO TABALE table_of_fields
  FROM B
  LEFT JOIN A
    ON A.key_fields = B.key_fields
  WHERE A.some_field_that_is_always_filled = ''. "if there won't be a record in A table this field will be empty

2. Delete record from the B table


DELETE b FROM TABLE table_of_fields.

That is the general idea.

Rgds

Mat

Re: DELETE FROM dbtab with functionality of MODIFY ?

Former Member — Mon, 17 Mar 2008 09:44:30 GMT

Hi,

Refer this code.

loop at itab into wa.

delete itab1 where f1 <> wa-f1.

endloop.

update DB from table itab1.

Reward if helpful.

regards,

Ramya

Re: DELETE FROM dbtab with functionality of MODIFY ?

Former Member — Mon, 17 Mar 2008 09:48:33 GMT

Hi,

As per ur requirement,

Consider two itabs itab_L and itab_R.

Create one new itab: itab_Del_Recrds.

Get the records from itab_R which are not available in itab_L..

Collect all those records in itab_Del_Recrds. by checking like this,

Loop at itab_R.

Read itab_L into wa_itab_L where itab_L-field = itab_R-field.

If sy-subrc <> 0.

Append WA_itab_L to itab_Del_Recrds.

Endif.

EndLoop.

Now u get all the unavailable records(to be deleted) in this new itab itab_Del_Recrds.

Now use DELETE itab_R from itab_Del_Recrds.

I hope it will work fine.

Re: DELETE FROM dbtab with functionality of MODIFY ?

vinod_vemuru2 — Mon, 17 Mar 2008 09:50:26 GMT

Hi,

Through MODIFY statement u can't delete the data in the database/internal tables. Modify will work like this.

If the entry already exist in the data base then it will update the data base(Same as UPDATE statement). If entry is not there in the data base then it will insert that record(Same as INSERT statement).

U have to use DELETE statement for your requirement.

LOOP AT righttab INTO waright.

READ TABLE lefttab INTO waleft WITH KEY (Specify ur key fields).

IF NOT sy-subrc IS INITIAL.

DELETE rightdatabasetable FROM waright.

ELSE.

Do ur processing for INSER/UPDATE as usual.

ENDIF.

ENDLOOP.

Hope this will solve ur problem. Make sure that your wa has all the key fields of the database table.

Thanks,

Vinod.

Edited by: Vinod Kumar Vemuru on Mar 17, 2008 3:22 PM