topic Re: ABAP SQL DELETE Statement:SubQuery in Application Development and Automation Discussions

ABAP SQL DELETE Statement:SubQuery

Former Member — Wed, 06 Jan 2010 13:31:12 GMT

HI Expert.

I need to write ABAP statement for following query

TABLE1 IS INTERNAL TABLE and TABELE2 is database table.

DELETE TABLE1 WHERE KEY1 IN (SELECT KEY1 FROM TABLE2)

thanks

Ajay

Edited by: Ajay Kumar on Jan 6, 2010 7:01 PM

Re: ABAP SQL DELETE Statement:SubQuery

Former Member — Wed, 06 Jan 2010 13:36:15 GMT

first fetch the table2 into a table or range table.

then use that range table in that DELETE ITAB WHERE KEY IN <RANGE>..

Re: ABAP SQL DELETE Statement:SubQuery

Former Member — Wed, 06 Jan 2010 13:38:54 GMT

Thanks Prakash

I would really apreciate if u can give me sample code as I have no much exp in ABAP.

thanks

Re: ABAP SQL DELETE Statement:SubQuery

Former Member — Wed, 06 Jan 2010 13:47:15 GMT

take those in select-options or another internal table and delete

for example

SELECT-OPTIONS: S_MATNR FOR MARA-MATNR.

DELETE IT_MARA WHERE MATNR NOT IN S_MATNR.

Please check the code below.

 
TABLES : mara.

TYPES :
BEGIN OF ty_mara,
 matnr TYPE mara-matnr,
END OF ty_mara.

DATA :
  it_mara TYPE STANDARD TABLE OF mara.


SELECT-OPTIONS :
   s_matnr FOR mara-matnr.

START-OF-SELECTION.

  SELECT matnr
         FROM mara
         INTO TABLE it_mara.


  IF sy-subrc = 0.

    DELETE it_mara WHERE matnr NOT IN s_matnr.

  ENDIF.

Let me know if you need furtehr info

Regards

Satish Boguda

Re: ABAP SQL DELETE Statement:SubQuery

Former Member — Wed, 06 Jan 2010 16:57:35 GMT

Please proceed as follows :

1. Fetch records from table 2 in internal table A

2. Loop on internal table A and keep the values of Key1 in seperate internal table B.

3. Use Sort and delete adjacent duplicates from Internal table B.This internal table is your range table.

4. Now , you can delete the records from internal table 1, using range.

Also, when you construct range pass SIGN parameter as EQ.

Hope this helps you.

Re: ABAP SQL DELETE Statement:SubQuery

Former Member — Thu, 07 Jan 2010 07:21:33 GMT

done