Re: SQL Locking...

[R Bruce Hoffman <bruce.hoffman@xxxxxxxxxxxxxxxxx>]

well, yes, but SQL is a "set at a time" operation. You are asking to
update a set of records from another set of records... so... SQL asks
the question... "what happens to the set update if b.mtordn changes
during the update operation?"

click... lock.



On Fri, 2006-11-17 at 12:39 -0600, Turnidge, Dave wrote:
> I have a fellow programmer who has this code in a CL program:
>
> Update OEP013W a set a.wkclosr = 
>       (select mtclon from OETITLP b where a.wkorder = b.mtordn)
>       where a.wkclosr = 0
>
> Where OEP013W is a work file in QTEMP;
> OETITLP is a production PF.
>
> If someone is in the record (locking it) this statement fails.
>
> If you do the sub-SELECT statement by itself with someone locking the
> record, it doesn't fail.
>
> Does a SELECT in an UPDATE statement have different rules?
>
> It was our understanding that a SELECT did not lock, or require a lock,
> and we assumed would not be inhibited by someone else locking a record
> in that file.
>
> TIA
>
> Dave