Hi Set

Thank you for your comment and advice. I wasn't aware of the IN /
EXISTS distinction and I am still not sure that I fully understand it
but I am grateful to you for pointing it out.. Nevertheless the
problem for both my original and your improvement is that neither of
them work at all.

They both throw the same error message

"ISC ERROR MESSAGE:
lock conflict on no wait transaction
deadlock"

As I understand it the update all occurs within the context of a
single transaction and I therefore don't understand what the lock
conflict is.



--- In ib-support@y..., Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

> Hi John.
>
> I'm not certain what your problem is and don't know whether my

suggestion

> will help or not - but are you aware that your subselect is

executed once

> for every row in your table? Changing to
>
> update JOBS set JOB_IS_ACTIVE = 'N'
> where exists(select 1 from SP13
> where JOBS.JOB_NUM = SP13.JOB_NUM
> and SP13.HAS_WIP='N'
> and SP13.MTHS_AGO_LAST_TRAN is null
> and SP13.MTHS_AGO_OPENED > 48)
>
> could improve execution speed drastically (in general, it is a bad

idea to

> use IN followed by a subselect - the more rows the subselect

returns, the

> slower the execution).
>
> Whether changing your update statement to what I suggest will help

you get

> rid of - or change - your error, I simply do not know. Try it and

report

> back to us the results.
>
> Set