cbll_lam wrote:

> Svein Erling Tysvaerwrote:
>
>> 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)
>
> Thank you all! I test it works although I still do not understand why
> it work, because I'm not good in working with subquery.

What the query does is to do a check for every record in A. If it finds
a record in B where B.Y has the same value as A.X, then it knows that it
should update A.

> Will it fail if 2 different alias not set for tableB?
> eg.
> update a
> set a.p=(select b.q from b where b.y=a.x)
> where exists(select 1 from b where b.y=a.x)

Either you get an error message or it works (in this case). I just used
two different aliases because that's what I normally do to not even have
to think about ambiguity (Firebird has become stricter and stricter in
this respect, and many queries that used to give you unexpected results,
now causes errors).

> Which method, this and the SP suggested by Adam, is faster? I meant
> execution speed for larger table.

I think that depends on the size of both a and b. If a is small and b is
huge, then the query will be the fastest, if b is small and a is huge
(probably the most common for this kind of queries), then the stored
procedure will be faster.

Set