February 3, 2005 at 4:13 pm
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). However, I want to enforce this check so that PKOutRec is unique which will in turn ensure my logic for this UPDATE works correctly.
Any ideas if this is possible and if so, how?
Thanks.
February 3, 2005 at 4:34 pm
Since this requires a correlated update you should probably post the DDL of the tables and specify what keys join them together.
This (untested) SQL would handle the correlated update but does not check if the PkOutRec has already been assigned, because it is not clear if you want the "already Assigned" check to be global to the whole table, or constrained by each key set.
UPDATE
EnrollmentSchool2
SET
PKOutRec = vtable.Min_EnrID
FROM
EnrollmentSchool2 As ens2
INNER JOIN
(
SELECT MIN(enr.EnrId) AS Min_EnrID,
ISMid,
Date
FROM
Enrollment As enr
WHERE
Active = 0
) vtable
ON
(vtable.ISMId = ens2.ISMId and
vtable.Date = ens2.StartDate )
February 4, 2005 at 12:19 am
Hi and many thanks for your reply.
>but does not check if the PkOutRec has already been assigned, because it is not clear >if you want the "already Assigned" check to be global to the whole table, or constrained >by each key set.
At first, I would have said the whole table because there should only be a single instance of the value of PkOutRec in the whole table. However, it is across the value of ISMid that the code is really concerned with - there are circumstances when due to the order of the date value, the same record is getting processed more than once (ie., PkOutRec is being "seen" more than once) so, I want to eliminate this possibility by having the UPDATE check whether PkOutRec (in the guise of MIN(EnrID)) has already been inserted in the column PkOutRec and if it has, the code moves to the "next" record in the seqence.
I really appreciate your assistance.
Best,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply