Update one table based on criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update one table based on criteria

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2011-09-06 : 15:28:01

What I need to do is update tblPermTask from tblPermTaskTemp where the IntQuestionId and the strssn no not 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 temptable

I tried a merge but it tells me I cannot do an update in a WHEN NOT MATCHED
MERGE into tblPermTask as pt
USING
(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)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-06 : 22:45:01

can you show some sample data from tables and then explain how you want update to having giving expected output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2011-09-07 : 09:03:29

Hi, Ok here is teh tblPermTask Table, So the tblPErmTempTask would have all the same fields except the intsrpId. So teh expectabtion is this if the intQuestionId has the bitFilterOnly = true. What I want to happen is to close out records in tblPermTask from tblPermtaskTemp if the records no longer show up in the temp table. So if intquestion 4 and 5 with the same StrSSN do not show up in the temp table in the next days run I want to close out the associated record in tblPermTask becuase it is no loner a problem. teh code below works to a degreee in that it closes records I do not want it to close. So if intquestionId's 2 and 3 do not have the bitFitleronly and will never reside in teh temp table its closing them out instead of closing out the questions with the bitfilteronly field.

intPermTaskId intquestionId intSrpId intPositionId dtFound strssn ......
1 2 3 221 09/07/2012 00000000
2 3 3 221 "" ""
3 4 3 222 "" 0101010101
4 5 5 224 "" 0202020202
5 6 5 225 "" 0303030303

UPDATE tblPermTask
SET dtCompleted = getdate(), strClosedBy = 'SYSTEM'
WHERE intQuestionId + strSSN Not IN
(SELECT pt.intQuestionId + pt.strSSN
FROM tblPermTask pt LEFT JOIN tblPermTaskTemp t ON t.intQID = pt.intQuestionId and t.strSSN = pt.strSSN LEFT JOIN
tblSRPQuestion q on q.intQuestionId = pt.intQuestionId
WHERE bitFilterOnly = 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources