Forum Stats

  • 3,760,436 Users
  • 2,251,705 Discussions
  • 7,871,120 Comments

Discussions

SQL 'Not In'

503828
503828 Member Posts: 25
edited Jun 3, 2014 8:52AM in SQL & PL/SQL

I'm  A SQL newbee looking for advice on a query:

(Oracle 11g R2)

I'm trying to delete all records from TABLE_A when the string in COLUMN_1 or COLUMN_2 doesn't match the string in COLUMN_1 of TABLE_B.

In other words, if either of the values from TABLE_A match, I DO NOT want to delete that record.

Additionally, if COLUMN_3 of TABLE_A is flagged ('X'), don't delete that record under any circumstances.

The query I have looks like this but does not produce the correct result:

DELETE FROM TABLE_A WHERE COLUMN_3 != 'X'

AND COLUMN_1 NOT IN (SELECT COLUMN_1 FROM TABLE_B) 

AND COLUMN_2 NOT IN (SELECT COLUMN_1 FROM TABLE_B);

Thanks in advance for your assistance.

Alex

Tagged:
Karlheinz Dressen

Answers

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown

    Sounds like you might want ORs instead of ANDs.

    But be careful on the NOT IN clause.  If even 1 row of the subquery is a null, you will not get any matches.

    You should add ...where column_1 is not null.

  • 503828
    503828 Member Posts: 25

    Thank you for your response.

    I will keep the 'Null' values in mind.  Fortunately, the subquery will not return null values.

    Regarding switching to 'OR's,  I'm confused by this, wouldn't that cause a problem with subquery results if I want either of the values from TABLE_A match TABLE_B?

    This AND OR thing is killing me!   

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    edited Jun 2, 2014 5:10PM

    Sample data and results would have been nice but try this:

    delete table_a a
    where  a.column_3 != 'X'
    and not exists (select 1
                    from   table_b b
                    where  b.column_1 = a.column_1
                    or     b.column_1 = a.column_2
                   )
    ;
    
    

    Edit: I changed it to AND not exists.

    Karlheinz Dressen
  • Unknown
    edited Jun 2, 2014 5:44PM
    The query I have looks like this but does not produce the correct result:

    So what is your reason for not posting the results so that WE can see them? Post the results, the data that was used and tell us why the results are not correct.

    I will keep the 'Null' values in mind.  Fortunately, the subquery will not return null values.
    
     

    Maybe it will - maybe it won't. Maybe it won't today but tomorrow it will.

    The point SomeoneElse was trying to make is that you should NOT make assumptions like that when you write code. Write your code to deal properly no matter WHAT the data might be. That means the code should work properly even if the data DOES HAVE null values. It also means that anyone reading your code should be able to KNOW, not guess, what should happen IF there are null values in the data.

    Regarding switching to 'OR's,  I'm confused by this, wouldn't that cause a problem with subquery results if I want either of the values from TABLE_A match TABLE_B?
    This AND OR thing is killing me!
     

    You are confusing yourself because you state the problem definition using OR but then write the code using AND. Does that make sense to you?

    Write your code to conform to the problem definition.

    In other words, if either of the values from TABLE_A match, I DO NOT want to delete that record.

    That says 'IF column_1 OR column_2 matches. . .'. So write your code the same way:

    IF (column_1 matches OR column_2 matches) THEN . . .

    The implement the NOT by negating that compound condition.

    Karlheinz Dressen
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,091 Red Diamond

    Hi,

    503828 wrote:
    
    I'm  A SQL newbee looking for advice on a query:
    (Oracle 11g R2)
    
    I'm trying to delete all records from TABLE_A when the string in COLUMN_1 or COLUMN_2 doesn't match the string in COLUMN_1 of TABLE_B.
    In other words, if either of the values from TABLE_A match, I DO NOT want to delete that record.
    Additionally, if COLUMN_3 of TABLE_A is flagged ('X'), don't delete that record under any circumstances.
    
    The query I have looks like this but does not produce the correct result:
    
    DELETE FROM TABLE_A WHERE COLUMN_3 != 'X'
    AND COLUMN_1 NOT IN (SELECT COLUMN_1 FROM TABLE_B) 
    AND COLUMN_2 NOT IN (SELECT COLUMN_1 FROM TABLE_B);
    
    Thanks in advance for your assistance.
    
    Alex
    

    What's wrong with it?

    Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.

    If you're asking about a DML statement, such as DELETE, the sample data will be the contents of the table(s) before the DML, and the results will be state of the changed table(s) when everything is finished.

    Explain, using specific examples, how you get those results from that data.  Point out where your existing DELETE statement is doing the wrong thing with that sample data.

    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

    See the forum FAQ: https://forums.oracle.com/message/9362002#9362002

  • Karlheinz Dressen
    Karlheinz Dressen Member Posts: 34 Blue Ribbon

    BTW: in post 3, from "SomeoneElse", there is the statement, you need...

  • kendenny
    kendenny Member Posts: 1,269

    Not in (select ... ) will not work if there is a null value in your select. This will work:

    DELETE FROM TABLE_A WHERE COLUMN_3 != 'X'
    AND COLUMN_1 NOT IN (SELECT COLUMN_1 FROM TABLE_B WHERE column_1 IS NOT NULL)
    AND COLUMN_2 NOT IN (SELECT COLUMN_1 FROM TABLE_B WHERE column_1 IS NOT NULL);

  • kendenny
    kendenny Member Posts: 1,269

    I posted a reply a minute ago but it disappeared.

    Not in (select ...) will not work if the select has null values. To make your delete work you need this:

    DELETE FROM TABLE_A WHERE COLUMN_3 != 'X'
    AND COLUMN_1 NOT IN (SELECT COLUMN_1 FROM TABLE_B where column_1 IS NOT NULL)
    AND COLUMN_2 NOT IN (SELECT COLUMN_1 FROM TABLE_B where column_1 IS NOT NULL);

  • 503828
    503828 Member Posts: 25

    Thanks to all of you for your replies!

    Unfortunately, I can't supply any information regarding the actual query or data due to confidentiality issues (government work).  My applolgies; I know that makes it much more difficult to help.

  • Sven W.
    Sven W. Member Posts: 10,533 Gold Crown
    edited Jun 3, 2014 8:57AM

    Instead of NOT IN you can also use NOT EXISTS . THis would allow to move the comparison into the subquery and thereby combine both subqueries into one.

    +untested because of missing test case and sample data+

    DELETE FROM TABLE_A A
    WHERE A.COLUMN_3 != 'X'
    AND not exists (SELECT null
                         FROM TABLE_B B
                         where B.COLUMN_1 in (A.COLUMN_1,A.COLUMN_2)
                         )
    
    
    

    Be aware that you should always use table aliases. Just to make sure which table the column originates from.

This discussion has been closed.