>I thought this working in Fb 2.5, but maybe not.
>I have a zipcode table with multiples of some zipcodes. it gets

updated occasionally with brand new zipcodes.

>I want to merge it into a ref table with unique zipcodes.The code

without merge works:

>
>insert into ZipCodeRef (ZipCode, Longitude, Latitude)
> select distinct T2.ZipCode, T2.Longitude, T2.Latitude
> from ZipCodeDataBus T2
> where not exists
>(select first 1 ZipCodeRef.Zipcode from ZipCodeRef where

T2.ZipCode=ZipCodeRef.ZipCode)

> and T2.ZipCode <> '00000';
>
>Above works. Merge below fails:
>
>merge into ZipCodeRef T1
>using ZipCodeDataBus T2
>on T2.ZipCode = T1.ZipCode
>when not matched then
>insert (ZipCode, Longitude, Latitude) values (T2.ZipCode,

T2.Longitude, T2.Latitude);

>
>Message: isc_dsql_execute2 failed
>SQL Message : -803
>can't format message 13:197 -- message file

C:\WINDOWS\SYSTEM32\firebird.msg not found

>Engine Code : 335544665
>Engine Message :
>violation of PRIMARY or UNIQUE KEY constraint "UKZIPCODEREF_ZIPCODE"

on table "ZIPCODEREF"

>Problematic key value is ("ZIPCODE" = '00501')
>
>in the source table there are multiple instances of zipcode '00501'.

After the first insert,I would expect a match to be found, and the
subsequent >insert to be avoided.

>
>Is there something wrong with my SQL?

Since I rarely use MERGE and haven't even tried Fb3, please don't trust
my answer!

First, I can confirm that Fb 2.5 (at least the somewhat old version I
use) accepts ambiguous statements like yours.

I find your expectation of one insert going through and the other to be
eliminated slightly problematic. Say ZipCodeDataBus contained (random
numbers for longitude/latitude)

ZipCode Longitude Latitude
00501 34.59 45.54
00501 62.32 67.12

Since a table basically is an unordered dataset, what you say you expect
is that zip-code 00501 is inserted once and that the longitude/latitude
inserted should be random (amongst those appearing for 00501). I would
prefer if Firebird for queries like yours (*) actually checked for
values existing in the tables before the statement was executed rather
than the current state after halfway executing your query. Hence, I like
that Firebird reports the error you observe.

It may be that longitude and latitude doesn't vary between rows for the
same zipcode in ZipCodeDataBus (your successful query indicates that,
but there's no way Firebird can know that). If so, I'd recommend you to
tell Firebird that by modifying your query:

merge into ZipCodeRef T1
using (select distinct ZipCode, Longitude, Latitude from ZipCodeDataBus) T2
on T2.ZipCode = T1.ZipCode
when not matched then
insert (ZipCode, Longitude, Latitude) values (T2.ZipCode, T2.Longitude,
T2.Latitude);

HTH,
Set

(*) For statements containing WHEN MATCHED it can be somewhat harder to
prevent ambiguity. Firebird seems to select randomly when doing a small
test on Fb 2.5 - I'd probably preferred an error (although the main
problem was my ambiguous (test) query, I like to be told when I create
ambiguity)!