Subject Re: [firebird-support] Re: UPDATE AND DELETE WITH JOIN
Author Arno Brinkman

> 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),

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

So you should be able to use:

UPDATE emarketing.contacts
busname = substring(coalesce(NEW.entityname,''),1,100),
busstreet = substring(coalesce(replace(replace(replace(NEW.address, char(10),','),',',','),',,',','),''), 1, 100),
buscity = coalesce(,''),
busstate = coalesce(NEW.statecode,''),
buszip = coalesce(NEW.postalcode,''),
buscountry = coalesce(NEW.Countrycode,''),
buswebsite = substring(coalesce(,''),1,100),
optout = case when NEW.activeflag = 0 then -10 else 0 end,
groupbyindustry = coalesce(NEW.customertypecode, 'UNKNOWN'),
groupby1 = 'Prospect'
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.

Arno Brinkman

General database developer support:

Firebird open source database (based on IB-OE) with many SQL-99 features:

Support list for Interbase and Firebird users:

Nederlandse firebird nieuwsgroep: