Thanks to both Thomas and SET - you've got me going again!
With kind regards to you both,
John

> -------Original Message-------
> From: Thomas Steinmaurer <ts@...>
> Subject: Re: [firebird-support] SQL Update statement question in Firebird 1.5
> Sent: 29 Nov '06 17:09
>
> Hi John,
>
> > Just moved over to the Firebird SQL camp from the Microsoft SQL
> > camp ... so far so good!
> >
> > I would like advice please on how to do an update to a table where
> > the update statement involves a join.
> > Say table T1 has 2 integer columns (Key1 and Num1) and table T2
> > similarly has 2 integer columns (Key2 and Num2).
> > Table T1:
> > Key1 Num1
> > 1 7000
> > Table T2:
> > Key2 Num2
> > 1 654
> > and you wanted to update T1.Num1 to be the sum of the 2 number
> > fields, ie 7,654.
> >
> > In MS you would write:
> > update T1
> > set T1.Num1 = T1.Num1 + T2.Num2
> > from T1, T2
> > where T1.Key1 = T2.Key2;
> >
> > How do achieve the same result in Firebird 1.5?
>
> If there is a one to one relationship between T1 and T2, then you can
> use the following:
>
> update T1
> set
> t1.num1 = (select t1.num1 + t2.num2 from T2 where t1.key1 = t2.key2)
> where exists (select 1 from T2 where t1.key1 = t2.key2)
>
> --
> Best Regards,
> Thomas Steinmaurer
> LogManager Series - Logging/Auditing Suites supporting
> InterBase, Firebird, Advantage Database, MS SQL Server and
> NexusDB V2
> Upscene Productions
> [LINK: http://www.upscene.com] http://www.upscene.com
>