topic Re: Delete records from database in Application Development and Automation Discussions

Delete records from database

Former Member — Tue, 20 Feb 2007 09:53:12 GMT

Hi,

If I have say 1000 records in Mara table, and i have 999 records in internal table and i need to delete that one record from Mara which is not present in internal table, what's the best way to delete that one record from database table.

thanks

regds

venu

Re: Delete records from database

seshatalpasai_madala — Tue, 20 Feb 2007 09:59:40 GMT

Hi,

Since you have to analyze say 999 records its better to do that in he program than in the Database server. I would say get all into another internal table and find out the entry which is not there in your existing internal table and then delete that entry from the data base. Something like the following.

LOOP AT BIG_ITAB into wa.

READ TABLE ITAB INTO wa_1 from wa( You can use WITH KEY also).

if sy-subrc ne 0.

exit.

endif.

ENDLOOP.

DELETE DB_TAB FROM WA.

Regards,

Sesh

Re: Delete records from database

Former Member — Tue, 20 Feb 2007 10:02:10 GMT

Hi venu,

Like u want to delete all the records from the table the best way u can do is.......

Select all the records into an internal table and directly delete the whole data bsae table using this internal .........

This is what i understood if i am wrong please correct me..

Murthy

Re: Delete records from database

Former Member — Tue, 20 Feb 2007 10:10:24 GMT

Hi Venu

In your case Mara is a database table or an internal table?

if its a database table:

sort itab by matnr.

loop at itab.

delete <dbtable> where matnr <> itab-matnr.

endloop.

if its an internal table:

loop at itab_mara.

v_index = sy-tabix.

read table itab where matnr = itab_mara-matnr.

if sy-subrc <> 0.

itab_mara - flag = 'X'.

modify itab_mara index v_index.

endif.

endloop.

delete from itab_mara where flag = 'X'.

-- regards,

madhumitha

Re: Delete records from database

Former Member — Tue, 20 Feb 2007 10:17:10 GMT

Hi,

I know this works, but my records in database will be around 1 lakh and the records in my internal table is 99,995, and I need to delete only 5 records from db table. so i dont want to run loop at itab.

That's the problem..

Thanks

Venu

Re: Delete records from database

raviprakash — Tue, 20 Feb 2007 10:23:25 GMT

Hi Venu,

There is no keyword for MINUS operation in ABAP. Unfortunately we have to use LOOP only. To make is fast, we can sort the tables by key fields and then LOOP and READ.

SORT : ITAB_BIG,

ITAB_SMALL.

LOOP at ITAB_BIG.

READ TABLE itab_small with key....BINARY SEARCH.

IF sy-subrc NE 0.

delete ITAB_BIG.

endif.

ENDLOOP.

NOTE: I think thats the ebst solution i can come up with right now.

Thanks and regards,

Ravi.

POINTS are invited .

Re: Delete records from database

Former Member — Tue, 20 Feb 2007 14:01:59 GMT

Since it's a standard SAP table, don't do it.

Rob

Re: Delete records from database

Former Member — Tue, 20 Feb 2007 14:36:40 GMT

Just a small example program:

P.S. If MARA is really the table you want to delete records from, maybe better to use BAPI_MATERIAL_DELETE to flag it for deletion. And if you do that, then you only need to store the material numbers. Saves memory.

TABLES: mara.

DATA: wa_mara LIKE mara.

* Copy of the internal table, which is (I assume) the same as MARA.
TYPES: t_itab_keep   LIKE mara,
       t_itab_delete LIKE mara.
DATA: itab_keep      TYPE TABLE OF t_itab_keep WITH HEADER LINE.
DATA: itab_delete    TYPE TABLE OF t_itab_delete WITH HEADER LINE.
DATA: h_lines TYPE i.


SELECT * FROM mara UP TO 10 ROWS.
* TEST FILL itab_keep.
  DESCRIBE TABLE itab_keep LINES h_lines.
  IF h_lines < 9.
    MOVE mara TO itab_keep.
    APPEND itab_keep.
    CLEAR itab_keep.
  ENDIF.
* END OF TEST FILL itab_keep
  READ TABLE itab_keep WITH KEY matnr = mara-matnr.
  IF sy-subrc <> 0.            "Not in ITAB, must be deleted from MARA
    APPEND mara TO itab_delete.
  ENDIF.
ENDSELECT.

LOOP AT itab_delete.
  WRITE:/ itab_delete.
ENDLOOP.

* Be very careful (should lock too), and allow restore!
* DELETE mara FROM TABLE itab.

Message was edited by:

Edwin Vleeshouwers

Re: Delete records from database

Former Member — Tue, 20 Feb 2007 15:01:24 GMT

O.K pls dont try this other hen the testing client.

with in a loop You should not do it.

SELECT matnr matkl

FROM mara

INTO TABLE it_mara

WHERE matnr = p_matnr AND

matkl = 'HELLO' .

DELETE mara FROM TABLE it_mara.

Re: Delete records from database

Former Member — Tue, 20 Feb 2007 17:27:20 GMT

Try this below code.

I have simulated your itab by adding a flag .

Just run this code and finally your Itab1 holds only the records to delete from mara.

Reward me well.

Regards,

Sankar

report testmara.

tables: mara.

data : begin of itab1 occurs 0.

include structure mara.

data : flag.

data : end of itab1.

data : itab2 like mara occurs 0 with header line.

data : c_flag value 'X',countrec type i.

start of Simulation for your itab1.

select * from mara up to 100 rows into table itab1 .

itab1-flag = c_flag.

modify itab1 transporting flag where flag = space.

loop at itab1.

countrec = countrec + 1.

delete itab1 index sy-tabix.

if countrec = 5.

exit.

endif.

endloop.

End of simulation for your itab1.

select * from mara up to 100 rows appending table itab1 .

sort itab1 by matnr flag descending.

delete ADJACENT DUPLICATES FROM itab1

COMPARING matnr.

delete itab1 where flag = 'X'.

Re: Delete records from database

Former Member — Fri, 23 Feb 2007 04:02:40 GMT

To delete reord from database table we use DELETE statement.

ie it will delete single record where condition is met.

you can delete multiple records from database table by putting all the records which you want to delete in internal table.

delete SMARA from table itab.

here whenever you have internal table will be deleted from smara.

append internal table which you want to delete.

Re: Delete records from database

Former Member — Fri, 23 Feb 2007 04:09:52 GMT

hey sri

u r really awesome, u r answering lot of threads today , I really appreciate that.. keep going like this... you r putting good effort from your side.

~~Guduri

Re: Delete records from database

Former Member — Thu, 08 Mar 2007 09:50:59 GMT

use for all entries option in select and get that odd record into a work-area by giving not-equal condition in where clause like

<b>select (all key fields )

...........................

into work-area

for all entries in itab

where <all key-fields> ne itab-<all key-fields>.</b>

now use delete statement to delete this record from database.

<b>delete dbtab from work-area.</b>

Re: Delete records from database

Former Member — Thu, 08 Mar 2007 11:08:45 GMT

HI Gautam,

Check it in F1 help...

Kishore reddy vuppuluri