Just thought I'd tell you about an annoying experience with SQL Server I
just had.

Let's say you have a master-detail relationship between table M and D.
Now, you may want to update each M record with info from one specific D
record each. In SQL Server you generally do this using this syntax
(non-standard?):

update M
set ...
from M
inner join D on ...
where ...

What I did was that I accidentally created SQL that in selected more
than one D record for a few of the M records. I would expect SQL Server
to throw an exception in this case, just as it does when trying to
assign too long string to a varchar column.

But no! It silently goes ahead and updates M with... something...

I could accept if it would pick one D record in this case, and use that
to update M, even if it were "random" which D record actually gets
picked. But what it actually did was to MIX data from two D records!

It appear that it picks data from the "first" D record where the fields
are non-null, but if a field is null, it checks the next record. If
there's a non-null value there, hat's what it writes to M. So, if some
fields are null and some not, you end up with a mix of data from
different D records.

Now, as far as I know FB doesn't support this update ... from syntax,
but is there any other way that FB could end up mixing data from
multiple records in this subtle way instead of throwing an exception?

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