|Subject||Merge in Fb3 fails|
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
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?