Delete a table in a single shot using a single read statement

[former_member731892]

I am using two table zle_ca_id_tmp and likp.My requirement is create a program and below logic

If ZLE_CA_ID_TMP.VBELN is not in LIKP.VBELN then delete the row the zle_ca_id_tmp in a single shot.

This should be done in a single read statement in my requirement.

I am using the below code and facing a issue in select and delete statement.Kindly please help in modifying with the correct query.

Situation

table zle_ca_id_tmp has 12,500,000 rows and table likp has 700,000 rows

we have two tables zle_ca_id_tmp and likp so do one read using both tables.

Create a new program with the below

If ZLE_CA_ID_TMP.VBELN is not in LIKP.VBELN

Then delete the row from table ZLE_CA_ID_TMP

Either if the outbound delivery no longer exists in LIKP then delete it from table ZLE_CA_ID_TMP Or if the outbound delivery exists in LIKP then leave it alone in the table ZLE_CA_ID_TMP

This should clear about 12 million rows.

LIKP contains lots of deliveries which are not present on table ZLE_CA_ID_TMP

• as LIKP contains Inbound and outbound delivereis
• as ZLE_CA_ID_TMP contains outbound deliveries for customer orders

Therefore

• Need to select ZLE_CA_ID_TMP.VBELN
• Where ZLE_CA_ID_TMP.VBELN does not exist in LIKP.VBELN

My code:

SELECT a~vbeln INTO TABLE li_zle_ca_id FROM zle_ca_id_temp AS a WHERE NOT EXISTS ( SELECT b~vbeln FROM likp AS b WHERE b~vbeln = a~vbeln ) .

IF sy-subrc = 0.

DELETE zle_ca_id_temp FROM TABLE li_zle_ca_id.

COMMIT WORK.

Can you please help in modifying the query for my requirement.

[moshenaveh]

Welcome and thanks for visiting SAP Community to get answers to your questions. Check out our tutorial to get started in SAP Community: https://developers.sap.com/tutorials/community-start.html
By adding a picture to your profile you encourage readers to respond to your question: https://www.youtube.com/watch?v=46bt1juWUUM

[Sandra_Rossi]

What is your "issue in select and delete statement"? Is it the result of SELECT which doesn't select the lines you want or is it DELETE which doesn't delete?

Please use the debugger and tell us.

[joltdx]

We would really need more information about what really is the problem here to help the best. But you can start with removing the b~ in

WHERE NOT EXISTS (Select b~vbeln from LIKP

You never define the b, so that can't possibly work.

Next tip, if you're having problem with the DELETE is to make sure that the row type of li_zle_ca_id is matching the entire table key of your table. ABAP Documentation

[former_member731892]

Hello Sandra,

I am having issue in select and delete. It is not fetching the value and also sy-subrc gets failed for the condition.The requirement is using a single select and delete statement for the table zle_ca_id_tmp.vbeln is not in LIKP.vbeln then it should delete the records from zle_ca_id_tmp.Can you please help in modifying the query of select and delete.Thanks.

[raymond_giuseppi]

I will suppose you get in trouble due to high number of records to delete and triggered some dump?

Could you switch to a logic such as :

DATA subrc TYPE sy-subrc.
WHILE subrc = 0.
  DELETE FROM zle_ca_id_tmp
    WHERE NOT EXISTS ( SELECT vbeln FROM likp WHERE vbeln EQ zle_ca_id_tmp~vbeln )
    UP TO 100000 ROWS.
  subrc = sy-subrc.
  CALL FUNCTION 'DB_COMMIT'.
ENDWHILE.

[former_member731892]

Hi Raymond,

My functional team has asked to change the below.Can you please in this regards.

Select needs to be changed

LIKP contains lots of deliveries which are not present on table ZLE_CA_ID_TMP

• as LIKP contains Inbound and outbound delivereis
• as ZLE_CA_ID_TMP contains outbound deliveries for customer orders

Therefore

• Need to select ZLE_CA_ID_TMP.VBELN
• Where ZLE_CA_ID_TMP.VBELN does not exist in LIKP.VBELN

[Sandra_Rossi]

vinodhict123 Could you clarify why Raymond answer does not answer your question?

[former_member731892]

Hi Sandra, The code given by him is working but my functional has asked to do with below select statement first and then delete.

Select needs to be changed

LIKP contains lots of deliveries which are not present on table ZLE_CA_ID_TMP

• as LIKP contains Inbound and outbound delivereis
• as ZLE_CA_ID_TMP contains outbound deliveries for customer orders

Therefore

• Need to select ZLE_CA_ID_TMP.VBELN
• Where ZLE_CA_ID_TMP.VBELN does not exist in LIKP.VBELN
• Can you please help in this regards.

[raymond_giuseppi]

IN this case, just convert the DELETE statement in a SELECT statement into an internal table, and use a DELETE statement using the intyernal table.

You can load the data by batch and destroy it as in my example or switch to a cursor technique (OPEN CURSOR) and batch read (FETCH) this is basic Abap

[raymond_giuseppi]

Ask an abapper tp convert theDELETE statement of my sample into a SELECT INTO TABLE and then add a DELETE using this internaml table.

[former_member731892]

Hello Raymond,

I am using the below code but it is not working .Can you please in this regards.

WHILE subrc = 0.

SELECT vbeln FROM zle_ca_id_tmp INTO TABLE li_zle_ca_id WHERE NOT EXISTS ( SELECT vbeln FROM likp WHERE vbeln EQ zle_ca_id_tmp~vbeln ).

IF sy-subrc EQ 0. DELETE zle_ca_id_tmp FROM TABLE li_zle_ca_id.

subrc = sy-subrc.

CALL FUNCTION 'DB_COMMIT'. ENDIF. ENDWHILE.

WRITE: 'The records are deleted from table ZLE_CA_ID'.

[raymond_giuseppi]

Replace

SELECT vbeln FROM zle_ca_id_tmp

with

SELECT * FROM zle_ca_id_tmp

and use the whole database table for the internal table structure definition

move also

subrc = sy-subrc.

out of the if/endif block

[former_member731892]

Hello Raymond,

I have tried the code but it is not working for me. Can you please help in modifying the query for this requirement.

Thanks in advance!

[former_member731892]

Hello Raymond,

The code worked but my functional has asked me to do this in smaller chunks so commit after each 1000 rows. Can you please help in providing the code for this requirement. Thanks in advance!

[Sandra_Rossi]

VIJAY v SELECT ... FROM z... UP TO 1000 ROWS ...Please search a little bit the Web:

select maximum rows site:sap.com

(and the official ABAP documentation "UP TO" appears in 4th position for me)

[former_member731892]

Hello Raymond/Sandra,

I have used the below query.Can you please confirm for the same.

SELECT * FROM zle_ca_id INTO TABLE li_zle_ca_id PACKAGE SIZE 1000 WHERE NOT EXISTS ( SELECT vbeln FROM likp WHERE vbeln EQ zle_ca_id~vbeln ).

IF sy-subrc EQ 0.

DELETE zle_ca_id FROM TABLE li_zle_ca_id.

COMMIT WORK.

[Sandra_Rossi]

VIJAY v if you use PACKAGE SIZE, there must not be a IF sy-subrc = 0 and there must be a ENDSELECT. But if you want to use COMMIT WORK inside the "SELECT loop", SELECT ... ENDSELECT would short dump, instead you must use OPEN CURSOR WITH HOLD (important is WITH HOLD to retain the cursor after COMMIT WORK). (moreover please use CODE button as I do and as Raymond does)

DATA mycursor TYPE cursor.
OPEN CURSOR WITH HOLD mycursor FOR
    SELECT * FROM zle_ca_id PACKAGE SIZE 1000 WHERE NOT EXISTS ( SELECT vbeln FROM likp WHERE vbeln EQ zle_ca_id~vbeln ).
DO.
  FETCH NEXT mycursor INTO TABLE li_zle_ca_id.
  IF sy-subrc <> 0.
    EXIT.
  ENDIF.
  DELETE zle_ca_id FROM TABLE li_zle_ca_id.
  COMMIT WORK.
ENDDO.
CLOSE CURSOR mycursor.

[former_member731892]

Thanks for your support sandra.rossi / raymond.giuseppi .I have modified the query, tested and it is working well.

[former_member731892]

Hello raymond.giuseppi / sandra.rossi , I have one question for this issue.If I use the below code in my development,

SELECT * FROM zle_ca_id INTO TABLE li_zle_ca_id PACKAGE SIZE 1000 WHERE NOT EXISTS ( SELECT vbeln FROM likp WHERE vbeln EQ zle_ca_id~vbeln ).

IF sy-subrc EQ 0.

DELETE zle_ca_id FROM TABLE li_zle_ca_id.

COMMIT WORK.

WRITE: 'The records are deleted from table ZLE_CA_ID'.

ELSE.

WRITE: 'No records are deleted from table ZLE_CA_ID'.

ENDIF.

ENDSELECT.

What will happen say there are 1500 records as the above query will take 1000 records first since we are using the package size 1000 and will it take remaining 500 records next?

Can you please suggest any improvement in the above code to be done.

[Sandra_Rossi]

vinodhict123 SELECT ... ENDSELECT is called a SELECT loop. It means it loops at all packages. Please read the official ABAP documentation.But two important remarks:

• IF sy-subrc = 0 right after SELECT of a SELECT is completely wrong, just remove this useless IF, and move it after ENDSELECT so that to WRITE depending on at least one iteration (sy-subrc = 0) or none at all (sy-subrc <> 0).
• Did you test what happens if you have a second "package"? (more than 1 iteration) Doesn't it short dump? (because of COMMIT WORK, see my previous comment that you have ignored)

[former_member731892]

Hello sandra.rossi / raymond.giuseppi ,I have modified the query as per your suggestion.Kindly please find below.

SELECT * FROM zle_ca_id INTO TABLE li_zle_ca_id PACKAGE SIZE 1000 WHERE NOT EXISTS ( SELECT vbeln FROM likp WHERE vbeln EQ zle_ca_id~vbeln ).

DELETE zle_ca_id FROM TABLE li_zle_ca_id.

COMMIT WORK.

ENDSELECT.

IF sy-subrc EQ 0.

WRITE: 'The records are deleted from table ZLE_CA_ID'.

ELSE. WRITE: 'No records are deleted from table ZLE_CA_ID'.

ENDIF.

I have tested with less data.Since I dont have much test data in development system,I am not able to test further.Can

you please review and please let me know if any changes required.Thanks!

[Sandra_Rossi]

vinodhict123 You should use PACKAGE SIZE 1 and select 2 lines to delete to see whether COMMIT WORK works inside SELECT...ENDSELECT.

[former_member731892]

Hello sandra.rossi ,Sorry .Can you please give the code for the above case.Thanks in advance!

[Sandra_Rossi]

VIJAY v You said that you "have tested with less data". You need only 2 lines in table zle_ca_id to do a complete test to see whether COMMIT WORK works inside SELECT...ENDSELECT, by replacing temporarily "PACKAGE SIZE 1000" with "PACKAGE SIZE 1".

[former_member731892]

Thanks sandra.rossi ,I have done the testing and it is going to dump for the code in test environment so I will go with the OPEN CURSOR technique as per your suggestion.