Hi,

I've been working almost exclusively with Teradata database since last
summer but still monitor what is going on in Firebird world.
According to the Teradata docs the form of update stmt
update ...
from ...
is ANSI SQL 99 compliant.

Regards,
Tom

On 7/12/06, Arno Brinkman <fbsupport@...> wrote:
>
> 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
>
>
>


[Non-text portions of this message have been removed]