Subject Re: [ib-support] Re: Not in query
Author Svein Erling Tysvær
>I guess I goofed in the 1st SQL statement. It should be something more like:
>table2.PK FROM table2)
>Your method works, and so does the above. But I am clueless as to which is
>faster/more efficient. In the above, the second SELECT needs to be done once
>if properly optimized (Is that correct?). In your statement, the SELECT is
>performed for each iteration of the INSERT, but retrieves a singleton only.

I think Helen's suggestion will be much faster (she is one of the big
guru's and normally knows what she is writing about). Although executing
the second select once would suffice in your example, this select could
have a where clause depending on the current row - and I think IB executes
it for every iteration.

>I did some testing by defining tables1 and 2 as having a primary key and a
>non unique field. Table1 has 2 records with the same value for the 2nd
>field; table2 is empty. I tried to apply both SQL statements, however using
>the 2nd field as the criterion instead of the PK, and to my surprise, only 1
>record was inserted in both cases!
>So is the SELECT executed for each INSERT iteration?

Sorry, I don't understand what you are saying here. Unless you have a
SELECT DISTINCT it should retrieve all rows matching your criteria. Maybe
there is some kind of a rounding issue? Do you get two rows if you do a
simple select? Did you commit your transaction?