Skip to Content
author's profile photo Former Member
Former Member

Retrieve records in table B which are not present in table A through SELECT

Hello experts,

I hope you could help on this. Following is my sample: I've got two tables: A & B and their "possible" types; fields marked * are key...

Table A (internal table)

A-field1*

A-field2*

Table B (internal or transparent table)

B-field1*

B-field2*

B-deleted

Well, now, let's load table B two times...

--Load 1--

source table A contains...

field1 field2

1 a

1 b

1 c

2 a

2 b

Loaded table B from table A

field1 field2

1 a

1 b

1 c

2 a

2 b

Load 1 comments: here everything is ok, table B is equal to A contents after loading A->B

--Load 2--

source table A contains...

field1 field2

1 a

2 a

2 b

Loading table B from table A, need to mark the field "deleted" with an X

field1 field2 deleted

1 a

1 b X

1 c X

2 a X

2 b

Load 2 comments: BEFORE loading into B, I need to detect which records must be marked as deleted in table B. Records to be marked are those that are still in table B but not coming in table A contents.

This is what I need to know:

REQUIREMENT: Select must retrieve: All the records in table B which are not present in table A. For the given sample field1 and field2 are both keys.

IMPORTANT: I want to avoid a LOOP and get all the chunk from once, this means a SELECT is the nice to have choice.

Thanks a lot in advance for your help. Best regards,

Bernardo

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 10, 2014 at 04:46 PM

    Hi,

    First of all according to your logic, outcome has to be as below

    field1 field2 deleted

    1 a

    1 b X

    1 c X

    2 a

    2 b

    because only 1b & 1c is present in table B and not coming from Table A

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 10, 2014 at 05:21 PM

    Something along the lines of:

    select * from tableB as b

    where b~deleted ne 'X'

    and not exists ( select single * from tableA as a where a~field1 eq b~field2 and a~field2 eq b~field2 ).

    This will give you all the records in B that do not exist in tableA then you can update them and modify the table. It probably won't be incredibly fast, but if field1 and field2 are the full key in tableA maybe it won't be terrible...

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      I'm assuming you will fill the deleted field in TableB after you find all the records which exist in TableB but not TableA. So after the first time you run this query, there's no sense looking at records in TableB which you've already marked deleted.

      That query should give you exactly what you need... Give it a try.

  • author's profile photo Former Member
    Former Member
    Posted on Jan 10, 2014 at 04:28 PM

    Hi Bernardo,

    Please elaborate your question.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 10, 2014 at 04:30 PM

    Hello Bernardo,

    I'm really don't understand your question.

    Maybe change the name of tables/fields for the real fieldnames can help to understand.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 10, 2014 at 04:38 PM

    Hey guys, thanks for checking,


    This is what I need:


    I need a SELECT which retrieves: All the records in table B which are not present in table A. For the given sample field1 and field2 are both keys. Something like this (in SQL format):

    select B.field1, B.field2

    from tableB as B

    left join (select field1, field2 from tableA) A

    on B.field1 = A.field1

    and B.field2 = A.field2

    where

    A.field1 is null

    and A.field2 is null

    But of course I need it expressed in ABAP and having into account tableA is an internal table and tableB could be internal or transparent table.

    I hope this clarifies your doubts, please let me know. Thanks again.

    Cheers,

    Bernardo

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.