Hi Gary,
Can you explain what your UPDATE is doing/suppouse to do?
>
>I am using the following code :-
>
>
>UPDATE EnrollmentSchool2
>SET PKOutRec = (
>SELECT MIN(enr.EnrId)
>FROM Enrollment enr
>WHERE enr.ISMid = EnrollmentSchool2.ISMId
>AND enr.[Date] > EnrollmentSchool2.StartDate
>AND enr.Active = 0)
>
>
>What I would like to do (if possible) is, in the same update statement, check whether the value MIN(enr.EnrId) being assigned to PKOutRec has already been assigned to PKOutRec earlier in the update statement and make it a condition in the WHERE clause in the SELECT statement ie.,
>
>
>AND NOT MIN(enr.EnrId) IN SELECT PkOutRec FROM EnrollmentSchool2
>
>
>I realise I am expressing this incorrectly (SQL server tells me so <s>). However, I want to enforce this check so that PKOutRec is unique which will in turn ensure my logic for this UPDATE works correctly.
>
--sb--