At 03:41 PM 03-07-01 +0200, you wrote:

>Helen,
>lobolos reasoning is basically that if he/she uses
>insert into table2
> select * from table1 where (not (exists(select NotAUniqueKey
> from table2 where NotAUniqueKey = table1.NotAUniqueKey)))
>it will never insert more than one record for any NotAUniqueKey. The
>plausible explanation is that since the subselect is executed once for
>every row, the content of table2 will have changed before the second
>instance of NotAUniqueKey executes. I.e.
>
>if table1 contains
>
>ID NotAUniqueKey
>1 2
>2 3
>3 3
>
>and table2 is empty, then
>
>insert into table2
> select * from table1 where (not (exists(select NotAUniqueKey
> from table2 where NotAUniqueKey = table1.NotAUniqueKey)))
>
>will insert into table2
>
>ID NotAUniqueKey
>1 2
>2 3
>
>or
>
>ID NotAUniqueKey
>1 2
>3 3
>
>and not both ID 2 and 3. I have not tested this myself, but it is an
>interesting situation I've never thought about and would like it to be
>explained from someone like you.

Oh, I see... :))

The person to answer navel-gazers like this is Ann (closely watched by DB - she's the one with the tiara...)

My solution to such a problem is far too mundane - I wouldn't do it! As soon as things start to look even slightly arcane, I resort to a stored proc. :))

H.

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________