Falling out of a list 1

[stemp1ar]

Hello and TIA,

I would like to know how to find out which values are falling out of a list of values using Oracle 8.x.

Example

select * from table where col1 in ( val1,val2,val3... )

How do I find all of the values in the list that are not in the table?

 

[tlbroadbent]

The ANSI syntax follows. I don't know if Oracle uses the same syntax.

Select * From table Where col1 NOT IN ( val1,val2,val3... )
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[stemp1ar]

Thank you for the reply, but I believe I phrased the question incorrectly...

Select * From table Where col1 NOT IN ( val1,val2,val3... )

will give me every item in the table that is not in the list of values.

Let me try again...
If I do an update to a table using a list of primary keys that I would like to do the update to, how do I know which of the primary keys in the list did not do an update?

Ex.

update table
set col2 = 'X'
where pkey in (list of 500 pkeys)

and only 400 of the pkeys were updated...how do I find the 100 pkeys that did not get updated?

 

[carp]

Well I think the original solution will tell you.
If you want to know which rows don't get updated, all you have to do is take the complement of your WHERE clause.
In your example, you update all rows
where pkey in (list of 500 pkeys)
To find out which rows DON'T get updated, you would do a query with
where pkey NOT in (list of 500 pkeys)

I believe your original question was fine, and tlbroadbent gave you an excellent solution.

 

[karluk]

I read stemp1ar's question differently. I think he wants a subset of (val1, val2, ...) consisting of values not found in the table. For example, val3, val8, and val13 are in the list, but not represented in the table. Stemp1ar wants a query that returns val3, val8, and val13. The "not in" syntax won't help here.

Offhand, I can't think of a particularly good way of doing this. If you are willing to go to the overhead of creating a second table, say "list_values", to store the possible values, you could do something like this

select list_item from list_values
where list_item not in
(select col1 from your_table
where col1 in (val1, val2, val3, ...))

 

[stemp1ar]

Sorry for the confusion...Yes Karluk is correct in what I am looking for. I will try your suggestion...

 

[carp]

Aha! Now I understand.
Yes, I have to agree with Karluk (as usual!) - you will either have to create a separate table or approach this via PL/SQL.

 

[tlbroadbent]

Thanks to Karluk for the insight and solution.

His idea for a second table is really worth the trouble - if not in thie current project, for future consideration. A list of 500 entries for the IN clause seems like a large number. At some point, the RDMS may choke on a large list. Performance can and probably will be very poor with such a query.

If the items from the IN list were entered in a table then the Update query could utilize the table to identify records. This should be more efficient because you can take advantage of what the RDMS does best - handle relations and sets of data.
[tt]
UPDATE table1 SET col2 = 'X'
FROM table1 INNER JOIN table2
ON table1.pkey = table2.key
WHERE table1.col2<>'X'[/tt] Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.