Subject Re: [firebird-support] Re: update from 2 tables
Author Svein Erling Tysvaer
Alan McDonald wrote:
>> --- In, "cbll_lam" <cbll_lam@...>
>> wrote:
>>> Thank you for pointing out my mistake. I must mixing it up with
>>> something else.
>>> It begs another question: how to do it in FB?
>>> Any suggestion will be appreciated.
>>> regards,
>>> bill
> what about
> update a set a.p=(select b.q from b where b.y=a.x);

There is actually a slight problem with this. If a.x doesn't match any
b.y, then a.p will be set to null and not left at the value it
originally was. Of course, that may be what he wants, but what would be
more common to want is

update TableA a
set a.p=(select b.q from TableB b where b.y=a.x)
where exists(select * from TableB c where c.y=a.x)