topic Re: Delete a table in a single shot using a single read statement in Application Development and Automation Discussions

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

Former Member — Sun, 28 Feb 2021 07:13:13 GMT

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.

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

moshenaveh — Sun, 28 Feb 2021 07:13:46 GMT

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

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

Sandra_Rossi — Sun, 28 Feb 2021 08:10:56 GMT

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.

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

joltdx — Sun, 28 Feb 2021 18:26:33 GMT

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

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

Former Member — Mon, 01 Mar 2021 08:52:55 GMT

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.

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

RaymondGiuseppi — Mon, 01 Mar 2021 09:15:36 GMT

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.

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

Former Member — Mon, 01 Mar 2021 13:22:33 GMT

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

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

Sandra_Rossi — Mon, 01 Mar 2021 17:22:00 GMT

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

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

Former Member — Tue, 02 Mar 2021 08:12:04 GMT

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.

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

RaymondGiuseppi — Tue, 02 Mar 2021 08:37:36 GMT

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

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

RaymondGiuseppi — Wed, 03 Mar 2021 08:54:04 GMT

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

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

Former Member — Wed, 03 Mar 2021 09:04:49 GMT

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'.

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

RaymondGiuseppi — Wed, 03 Mar 2021 09:36:28 GMT

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

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

Former Member — Wed, 03 Mar 2021 09:56:18 GMT

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!

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

Former Member — Thu, 04 Mar 2021 13:07:18 GMT

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!

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

Sandra_Rossi — Thu, 04 Mar 2021 14:33:04 GMT

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)

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

Former Member — Fri, 05 Mar 2021 06:59:37 GMT

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.

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

Sandra_Rossi — Mon, 08 Mar 2021 09:55:10 GMT

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.

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

Former Member — Wed, 10 Mar 2021 09:19:00 GMT

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

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

Former Member — Thu, 11 Mar 2021 05:39:22 GMT

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.