Helen Borrie wrote:

> >I would have expected this to work:
> >DELETE
> >FROM MYTABLE T
> >WHERE
> >T.DATE=
> >(SELECT MIN(Q.DATE) FROM
> >MYTABLE Q WHERE T.NUMPAT = Q.NUMPAT )
> >Why doesn't it work? What error do you get?
>
> I would expect it to work, but too well! There's nothing here to prevent
> this statement from continuing to cycle through until it has deleted all
> of the records in the table (except those where NUMPAT or "DATE" were
> NULL). At the very least I would want to test this theory on a copy of
> the database!

Oh, is this the gotcha where a FB query sees its own effects, i.e. when
one record is deleted, and the subquery is reevaluated for the next
record, the previously deleted record is not seen by the subquery, and
so might decide that the next record, although newer, should also be
deleted?

I'm qurious. What does the appropriate SQL standard have to say about
this behavior, and if it says that this is wrong, are there any plans to
change FB to adhere to the standard?

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64