Hi Fabio,

I have tried the code you have suggested and whilst it looks very promising indeed, it isn't updating any records at all. Any suggestions why this might be? There must be something not quite right?

Thanks,



>>Hi
>>
>>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.
>>
>>Thanks,
>
>Info are insufficient ( PKEY(EnrollmentSchool2) IS ? Assume: ISMId):
>

>UPDATE EnrollmentSchool2
>   SET	PKOutRec = X.MIN_EnrId
>FROM (SELECT e2.ISMId
>       ,     MIN_EnrId=MIN(enr.EnrId)
>           FROM Enrollment enr
>              JOIN EnrollmentSchool2 e2
>              ON enr.ISMid = e2.ISMId AND enr.[Date] > e2.StartDate AND enr.Active = 0
>           GROUP BY e2.ISMId
>           HAVING MIN(enr.EnrId) NOT IN (SELECT PkOutRec FROM EnrollmentSchool2)
>	) X WHERE X.ISMId=EnrollmentSchool2.ISMId
>

>
>Fabio