Hi Folks,

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),
buscity = coalesce(i.city,''),
busstate = coalesce(i.statecode,''),
buszip = coalesce(i.postalcode,''),
buscountry = coalesce(i.Countrycode,''),
buswebsite =
substring(coalesce(i.website,''),1,100),
optout = case when i.activeflag = 0 then -10
else 0 end,
groupbyindustry =
coalesce(i.customertypecode,'UNKNOWN'),
groupby1 = 'Prospect'
from inserted i
where emarketing..contacts.ExLinkID like 'C|' +
cast(i.customerid as nvarchar(10)) + '|%'


is used to replicate company information between two different systems
in a trigger. to do the same without the from clause would require sub
queries for every "set" element, in this case 10 different subqueries,
all differing in only the field returned.

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

the syntax could be clearer i believe, but this type of muti field
update is a useful tool and should be included in firebird somehow.

Regards
Ian Newby