Hi,

> Although I am not keen on the SQL syntax, and have to look it up every
> time I want to use it, it does allow you to make more efficient
> queries than you can do using standard SQL.
>
> Ie the following query (in MS)
>
> update emarketing..contacts
> set
> busname =
> substring(coalesce(i.entityname,''),1,100),
> busstreet =
> substring(coalesce(replace(replace(replace(i.address, char(10),','),',
> ',','),',,',','),''), 1, 100),

<snip...>

IIRC the "inserted" context is the same as our "NEW" context in our triggers.

So you should be able to use:

UPDATE emarketing.contacts
SET
busname = substring(coalesce(NEW.entityname,''),1,100),
busstreet = substring(coalesce(replace(replace(replace(NEW.address, char(10),','),',',','),',,',','),''), 1, 100),
buscity = coalesce(NEW.city,''),
busstate = coalesce(NEW.statecode,''),
buszip = coalesce(NEW.postalcode,''),
buscountry = coalesce(NEW.Countrycode,''),
buswebsite = substring(coalesce(NEW.website,''),1,100),
optout = case when NEW.activeflag = 0 then -10 else 0 end,
groupbyindustry = coalesce(NEW.customertypecode, 'UNKNOWN'),
groupby1 = 'Prospect'
WHERE
ExLinkID like 'C|' + cast(NEW.customerid as nvarchar(10)) + '|%'

> This is the sort of usage of the MS syntax that makes sense,
> updating multiple fields based on a join.

Probably there are situations were this syntax would be nice (preferable SQL-standard syntax), but i had never need it.
Did you already check if there's a feature-request in the firebird tracker else you could add it there.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features:
http://www.firebirdsql.org
http://www.firebirdsql.info

Support list for Interbase and Firebird users:
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep:
news://newsgroups.firebirdsql.info