Hello,
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?
Thanks
