What I need to do is update tblPermTask from tblPermTaskTemp where the IntQuestionId and the strssn Match and they no longer reside in the tblPermTaskTemp table and where the intQuestionId has a bit called bitFilterOnly. What I am doing is closing out records if they no longer exist in the temp tables
I tried a merge but it tells me I cannot do an update in a WHEN NOT MATCHED
MERGE into tblPermTask as pt
(Select pt.intQId, strSSN from tblPermTaskTemp pt INNER JOIN
tblSRPQuestion q on q.intquestionId = pt.intQId Where bitFilterOnly = 1) as t
on t.intQId = pt.intQuestionId and pt.strssn = t.strSSN
WHEN NOT MATCHED THEN
UPDATE set dtCompleted = getdate(), strClosedBy = 'SYSTEM';
This is based on the old systems when bitFilterOnly did not exist but it does not work with the bitFilter only. It updates all teh records that do not have the bitFilterOnly which I do not want it too.
update tblPermTask set dtCompleted = getdate(), strClosedBy = 'SYSTEM'
where dtCompleted is null and convert(varchar(10), intQuestionId) + '|' +
strSSN not in (select convert(varchar(10), intQId) + '|' + strSSN from
tblPermTaskTemp pt INNER JOIN
tblSRPQuestion q on q.intquestionId = pt.intQId
Where bitFilterOnly = 1)