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
http://www.upscene.com