topic Re: Delete statement in Application Development and Automation Discussions

Delete statement

Former Member — Fri, 15 Sep 2006 13:33:55 GMT

Hello Friends,

I want to delete records comparing one field for duplicates,

I am using

DELETE ADJACENT DUPLICATES FROM it_knvp COMPARING kunn2.

But I want to delete all the records if duplicate exist, but the above statement is keeping one record in the internal table and deleting the rest for duplicates.

I am thinking of reading the internal table into a work area and delete the internal table.

but is there a easier way to delete it.

Let me know for any questions,

Shejal Shetty.

Re: Delete statement

former_member404244 — Fri, 15 Sep 2006 13:38:08 GMT

Hi Shejal,

u can do like this

DELETE ADJACENT DUPLICATES FROM it_knvp COMPARING all fields.

Regards,

Nagaraj

Re: Delete statement

Former Member — Fri, 15 Sep 2006 13:38:09 GMT

Hi,

you can try it this way :

sort it_knvp on kunn2.

delete adjacent duplicates from it_knvp .

regards,

Shikha

Re: Delete statement

Former Member — Fri, 15 Sep 2006 13:38:38 GMT

You can do one thing...

sort itab1 by kunn2.

itab2[] = itab1[].

sort itab2 by kunn2.

loop at itab1.

clear cnt.

loop at itab2 with key kunn2 = itab1-kunn2.

cnt = cnt + 1.

endloop.

if cnt >= 2.

delete itab1 where kunn2 = itab2-kunn2.

endif.

endloop.

itab1 will have the resultant.

Regards,

Raj

Message was edited by: Rajasekhar Dinavahi

Re: Delete statement

Former Member — Fri, 15 Sep 2006 13:40:05 GMT

Hi,

kindly check the following code:

DELETE ADJACENT DUPLICATES FROM it_knvp COMPARING kunn2.
IF sy-subrc = 0.
  IF NOT it_knvp[] IS INITIAL.
    DELETE it_knvp FROM 1.
  ENDIF.
ENDIF.

DELETE ... FROM idx1

If you specify FROM, all the table rows from the table index idx1 onwards are included.

idx1 must be a data object with type i.

If the value of idx1 is less than or equal to 0, a runtime error occurs.

If the value is greater than the number of table rows, no rows are deleted.

best regards,

Thangesh

Re: Delete statement

Former Member — Fri, 15 Sep 2006 13:43:04 GMT

Small modification to the post above so that only duplicate records are deleted from internal table.

sort itab1 by kunn2.

itab2[] = itab1[].

sort itab2 by kunn2.

loop at itab1.

clear itab2.

clear count.

loop at itab2 whith kunn2 = itab1-kunn2.

count = count + 1.

if count > 1.

delete itab1 where kunn2 = itab2-kunn2.

exit.

endif.

endloop.

itab1 will have the resultant.

Hope this helps.

Message was edited by: Imtiaz Ahmed

Re: Delete statement

Former Member — Fri, 15 Sep 2006 13:43:07 GMT

Try this:

SORT IT_KNVP BY KUNN2.

IT_KNVP_COPY[] = IT_KNVP[].

DELETE ADJACENT DUPLICATES FROM IT_KNVP COMPARING KUNN2.

LOOP AT IT_KNVP.

COUNT = 0.

LOOP IT_KNVP_COPY WHERE KUNN2 = IT_KNVP-KUNN2.

COUNT = COUNT + 1.

ENDLOOP.

IF COUNT > 1.

DELETE IT_KNVP.

ENDIF.

ENDLOOP.

Max

Re: Delete statement

Former Member — Fri, 15 Sep 2006 13:44:05 GMT

Thanks all,

Thangeswaran I want to delete all the records only if duplicate exist . However if duplicates does not exist I dont want to delete that single record.

Shejal.

Re: Delete statement

Former Member — Fri, 15 Sep 2006 13:44:35 GMT

Hi Shejal,

Try this way:

data: it_knvp_prev like line of it_knvp.

data: begin of itab occurs 0,

kunn2 like knvp-kunn2,

end of itab.

sort it_knvp by kunn2.

loop at it_knvp.

if it_knvp_prev eq it_knvp.

move it_knvp-kunn2 to itab-kunn2.

append itab.

clear itab.

endif.

it_knvp_prev = it_knvp.

clear it_knvp.

endloop.

sort itab.

delete adjacent duplicates from itab.

delete it_knvp where kunn2 in itab.

Regards,

Vivek

Re: Delete statement

Former Member — Fri, 15 Sep 2006 13:52:25 GMT

heve you seen my sample in previuos post:

SORT IT_KNVP BY KUNN2.

Save data

IT_KNVP_COPY[] = IT_KNVP[].

Delete duplicate keys

DELETE ADJACENT DUPLICATES FROM IT_KNVP COMPARING KUNN2.

Check which records have a duplicate key

LOOP AT IT_KNVP.

COUNT = 0.

LOOP IT_KNVP_COPY WHERE KUNN2 = IT_KNVP-KUNN2.

COUNT = COUNT + 1.

ENDLOOP.

IF COUNT > 1.

Here delete last record

DELETE IT_KNVP.

DELETE IT_KNVP_COPY WHERE KUNN2 = IT_KNVP-KUNN2.

ENDIF.

ENDLOOP.

Max

Re: Delete statement

Former Member — Fri, 15 Sep 2006 13:52:52 GMT

Hi Shejal,

Small change in above code:

instead of if it_knvp_prev eq it_knvp.

Regards,

Vivek

Re: Delete statement

Former Member — Fri, 15 Sep 2006 13:55:04 GMT

U can even try the below...

sort itab by kunn2.

loop at itab.

at new kunn2.

lctr = 0.

lkunn2 = itab-kunn2.

endat.

lctr = lctr + 1.

at end of kunn2.

delete itab where kunn2 = lkunn2.

endat.

Endloop.

U can use on change even if kunn2 is not your first field of internal table itab.

Re: Delete statement

Former Member — Fri, 15 Sep 2006 13:58:54 GMT

Hi,

DELETE ADJACENT DUPLICATES FROM it_knvp COMPARING kunn2.
IF sy-subrc = 0.
  IF NOT it_knvp[] IS INITIAL.
    DELETE it_knvp FROM 1.
  ENDIF.
ENDIF

In the above code, <b>if sy-subrc = 0</b> is used to check that condition...

So, first it deletes the duplicate records.

If duplicate records are deleted, then all the records will be deleted from it_knvp.

otherwise, it will not delete that single record.

Hope this helps!

best regards,

Thangesh

Re: Delete statement

Former Member — Fri, 15 Sep 2006 14:08:10 GMT

Thanks Everyone,

Thanks Thangeswaran. It works,

Shejal Shetty.