SQL 'Not In'

503828

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

SomeoneElse

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

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

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.

Unknown

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.

Frank Kulash

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

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

kendenny

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

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

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.

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.