How to shorten DELETE statement for DB tables?

Question

Hello is there a way to put this in one statement?

DELETE e_worklist where wbs_element  = '00000000000000000054TTO'.
DELETE e_worklist where wbs_element  = '00000000000000000056TTO'.
DELETE e_worklist where wbs_element  = '00000000000000000055TTO'.
DELETE e_worklist where wbs_element  = '00000000000000000261TTO'.
DELETE e_worklist where wbs_element  = '00000000000000000263TTO'.
DELETE e_worklist where wbs_element  = '00000000000000000262TTO'.

Depends on where the values come from... – vwegert Aug 15 '17 at 14:32 — vwegert, Aug 15 '17 at 14:32

Suncatcher · Answer 1 · 2017-08-15T19:33:03.337

The above proposed answers with IN are valid only for DB-tables:

DELETE FROM e_worklist where wbs_element IN ( '00000000000000000054TTO', '00000000000000000056TTO', '00000000000000000055TTO' ).

In your sample you put internal tables statements, and for them proposed IN syntax is invalid for literals. It works only with selection tables/ranges, which also need to be populated properly. So you can't just wrap literals into IN clause, you need to do smth like this:

ws_wbs-sign   = 'I'. 
ws_wbs-option = 'EQ'. 
ws_wbs-low    = '00000000000000000054TTO'. 
APPEND ws_wbs TO rt_wbs. 

DELETE e_worklist WHERE wbs_element IN rt_wbs.

which is cumbersome, indeed, and won't bring you conciseness.

However, as your patterns are monotonous, you can bring your fantasy and use regex, for example:

DELETE e_worklist WHERE wbs_element CP '00000000000000000*TTO'.

score 1 · Answer 2 · answered Aug 16 '17 at 07:28

Just give your another hint. Without any range tables, you can also achieve a better statement with FILTER and EXCEPT. Documentation is here.

You have to copy or make e_worklist as a sorted table.

data: 
    ls_worklist like line of e_worklist,
****Ajust your key if necessary
    lts_worklist like sorted table of ls_worklist with unique key table_line,  
    lth_wbs_element like hashed table of ls_worklist-wbs_element with unique key table_line.

lts_worklist = e_worklist.

****Add your other values if necessary
lth_wbs_element = value #( ( |00000000000000000054TTO| ) ( |00000000000000000263TTO| ) ). 

e_worklist = filter #( lts_worklist except in lth_wbs_element where wbs_element = table_line ).

Actually FILTER operator starts from 7.4. it is just another way of coding format — Haojie, Aug 16 '17 at 13:59

score 0 · Answer 3 · answered Aug 16 '17 at 15:30

Since it is an internal table, unlike with database commands, there is no real performance impact from getting the number of DELETE commands down. You can make it slightly more concise with:

DELETE e_worklist where wbs_element  =: '00000000000000000054TTO', 
                                        '00000000000000000056TTO',
                                        '00000000000000000055TTO',
                                        '00000000000000000261TTO',
                                        '00000000000000000263TTO',
                                        '00000000000000000262TTO'.

Although this works it is a seriously weird position for a chain statement and I’d discourage using this syntax. Even most experienced ABAP programmers will be nothing but confused by this. — Florian, Apr 01 '18 at 21:30

How to shorten DELETE statement for DB tables?

3 Answers3