--- In ib-support@y..., Helen Borrie <helebor@d...> wrote:

> 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 replied to this yesterday, I thought, but haven't seen reply appear
on the list ... The thought of orphaned fragments of SQL rules,
drifting through the ether, is somewhat frightening ... but I digress,
or hallucinate, or whatever.]

"Plausible", perhaps, in that it describes (as far as I recall),
InterBase's behaviour in this situation.

However, that behaviour does not conform to SQL standard.

According to the SQL standard, the effect of an "insert into ...
select from ..." statement should be as if you created a separate
result set containing all the rows that satisfy the "select from ..."
part of the query, and then took each of the rows from the
intermediate result set and inserted it into the target table.

[Pause while I find the SQL92 mumbo-jumbo again ...]

Here's the relevant bit from SQL92, subclause 13.8 <insert statement>,
General Rules, where "B" is the target table for the INSERT:

"3) The <query expression> is effectively evaluated before inserting
any rows into B.

4) Let Q be the result of that <query expression>.
Case:
a) If Q is empty, then no row is inserted and a completion condition
is raised: no data. [So sad, too bad]
b) Otherwise, for each row R of Q:
i) A candidate row of B is effectively created <... blah blah blah,
assign default values, blah blah blah assign values from select, blah
blah blah consider casting rules, blah blah blah ...>
iv) The candidate row is inserted into B."

Hope that helps,
db