ABAP SQL DELETE Statement:SubQuery

[Former Member]

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

[Former Member]

first fetch the table2 into a table or range table.

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

[Former Member]

Thanks Prakash

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

thanks

[Former Member]

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

[Former Member]

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.

[Former Member]

done