Subject Re: [ib-support] Newbie : Update with select statement
Author Svein Erling Tysvaer
Hi again Jenny!

You have to kind of split this into two and use a subselect:

UPDATE aktkonto
set batchid=(select new_batchid
from akt a1
where a1.aktid = aktkonto.aktid
and a1.EBV = 1)
where aktkonto.batchcode = 1
and exists(select 1
from akt a2
where a2.aktid = aktkonto.aktid
and a2.EBV = 1)

The exists is to make sure that batchid isn't updated if there should
happen to be any aktid that exists in aktkonto but not in akt. If this is
never the case, you do not need the last part.


At 03:02 27.08.2002 -0700, you wrote:
>UPDATE aktkonto
>set batchid=:new_batchid,
>from akt
>where akt.aktid = aktkonto.aktid
>and aktkonto.batchcode = 1
>and akt.EBV = 1