Solved

Update one table based on criteria

Posted on 2011-09-06
17
166 Views
Last Modified: 2012-05-12
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
	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)

Open in new window

0
Comment
Question by:kdeutsch
  • 10
  • 5
  • 2
17 Comments
 
LVL 28

Expert Comment

by:strickdd
ID: 36490823
Create a trigger on the temp tables that update the main table.

http://msdn.microsoft.com/en-us/library/ms189799.aspx
0
 

Author Comment

by:kdeutsch
ID: 36491048
I don' see how adding a trigger on the Db will help mw out in this situation.  I need to see if the records that have bitFilteronly = 1 that exist in tblPermTask but no longer exist in tblPermtaskTemp will be closed out.  A trigger would not know this to my knowledge.
0
 
LVL 28

Expert Comment

by:strickdd
ID: 36491122
According to your initial question, if a record is removed from the temp table, it should be closed out in the main table - "What I am doing is closing out records if they no longer exist in the temp tables"

So, to "no longer exist in the temp tables" they must have been deleted from those tables. In that case, you add a DELETE trigger to this table that will look for the corresponding record in the main table and close it out.
0
 

Author Comment

by:kdeutsch
ID: 36491209
Ok a further explaination is needed.  I do not delte any records out of the temp table.  i have a procedure that runs and finds problems on a persons record by runnig lost of sql code agianst databases.  Initially they go into the tblPermTaskTemp Db.  i then insert all new records that are not matched into the tblPermTask Db.  From here I need to find all teh records that are sitting in tblPermTask db that did not show up in the tblPermTaskTemp, which means the database of record was fixed and the problem shoul dno longer show up so i need to close out that task.  This is the only way i know tasks can be closed out.
0
 

Author Comment

by:kdeutsch
ID: 36491232
strickdd:
i could do this with a Cursor and selecting all records from tblPermtask with bitFilter and then checking one at a time to see if they exist in teh tblPermTaskTemp and update the record properly, but there there has to be a better way than a cursor as it would be very slow.
0
 
LVL 28

Expert Comment

by:strickdd
ID: 36491272
Ok, things are making more sense. Here is the general query to get the records that are in the permanent table and not in the temp table

SELECT *
FROM permTable
    LEFT JOIN tempTable ON tempTable.FKID = permTable.ID
WHERE tempTable.FKID IS NULL

You can also add this to the WHERE clause:

    AND bitFilter = 1

Then, you do your UPDATE based on this SELECT as the WHERE clause.
0
 

Author Comment

by:kdeutsch
ID: 36491324
strickdd:,

Of cannot use keys to each other tables because they will never corresond with each other becuase Temp table is deleted before the run to insert new data every night.  It would have tto be something like this, however it does not work, it return nothing and it shoud return about 256 records to check agianst the temp table.

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 pt.intPermtaskId IS NULL and pt.strSSN IS NULL and bitFilterOnly = 1
0
 

Author Comment

by:kdeutsch
ID: 36491342


This returns what I need it to return from the tblPermtask.  Now I just need to figure out how to compare it to the Temp table and if they do not exist ing the temp table then update the master record and close it out.

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
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 28

Expert Comment

by:strickdd
ID: 36491377
Sorry for the lack of clarity, when i said "General Form" I meant that it wouldn't be EXACTLY what you need. The concepts would be there, but the names would be different. In you case, the FKID corresponds to whatever column(s) you use to link the two tables together - whether explicitly a primary key > foreign key relationship or column(s) used to identify matches.

So, your UPDATE in "General Form" would be something like this:

UPDATE MasterRecord
SET MyCloseIndicator = 1
WHERE MasterRecord.ID 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 )
0
 

Author Comment

by:kdeutsch
ID: 36491447
Hi,
No I see what you are trying to do and I had something similar in this I have to match on teh intQuestionId and strssn and this is the only way I found to do it. however it closes out all records that do not have the bitFitlerOnly = 1 and leaves those records alone.

update tblPermTask set dtCompleted = getdate(), strClosedBy = 'SYSTEM'
        where dtCompleted is null and convert(varchar(10), intQuestionId) + '|' +
        strSSN EXISTS (select convert(varchar(10), intQId) + '|' + strSSN from
        tblPermTaskTemp pt INNER JOIN
            tblSRPQuestion q on q.intquestionId = pt.intQId
where      bitFilterOnly = 1)


Here is wehat I tried with what was suggested but I do not see how to lock onto both ID fields, If I try to use both in the query it errors me out, but it has to be on both the questionId and strssn.

This errors out.

Msg 116, Level 16, State 1, Line 7
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

UPDATE      tblPermTask
SET            dtCompleted = getdate(), strClosedBy = 'SYSTEM'
WHERE      intQuestionId 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)

This does not but updates records that do not have the bitFilterset to it. Its just missing teh strssn in the query.

UPDATE      tblPermTask
SET            dtCompleted = getdate(), strClosedBy = 'SYSTEM'
WHERE      intQuestionId IN
   (SELECT pt.intQuestionId
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)
0
 

Author Comment

by:kdeutsch
ID: 36491457
Ugh Need to learn to type


This does not but updates records that do not have the bitFilterset to it. Its just missing teh strssn in the query.


should be
This query updates the record set but only records that do not have the bitFilterOnly.  So its updateing the wrong records it shou dony be records with the bitFilteronly enabled.
0
 
LVL 8

Expert Comment

by:Ghunaima
ID: 36493675
Correct your query by replacing "EXISTS" with "NOT IN"
0
 
LVL 28

Accepted Solution

by:
strickdd earned 250 total points
ID: 36494991
Since you are selecting multiple fields with the IN statement, concatenate the fields, for example (replace SSNFIELDNAME with the correct value):

UPDATE      tblPermTask
SET            dtCompleted = getdate(), strClosedBy = 'SYSTEM'
WHERE      intQuestionId + SSNFIELDNAME IN
   (SELECT pt.intQuestionId + SSNFIELDNAME
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)
0
 

Author Comment

by:kdeutsch
ID: 36495257
Ok, its working but its not working, I will try to explain.  This is a partial structure as the whole thing would overwhelm
tblPermtask

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                                2                                5                             225                     ""        0303030303


So what is happening witht he code is this say question intQuestionId  4 and 5 have the bitFilterOnly on them and 2 and 3 do not.   2 and 3 would not be in the temp table becuase they came into this system via a set of questions instead of sql code hitting a database.  But what is happending witht eh code below is that it is clsoing out those questions that do not have the bitFitleronly and will never reside in the temp table and totally leavein  4 and 5 alone and doing nothing with them, even if I take them out of the temp table they do not get closed. If I reverse the Not In to an IN then it closed the bitfilters out but they stillexist in temp table.  Don't know how well I explained this.

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)
0
 

Author Comment

by:kdeutsch
ID: 36497301
Hi,
Ok had to do what I did not want to do, which is build a cursor to do it for me but it seems to run very quickly.  But this is the end result.

Declare @QId int, @SSN as varchar(9), @Count int

Declare Find_close Cursor FOR      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 and pt.strSSN <> '000000000';
            OPEN Find_Close
            
                  FETCH NEXT FROM Find_Close INTO @QId, @SSN
             
                  while @@FETCH_STATUS = 0
                  BEGIN
                        Select @Count = COUNT(intQId) from tblPermTaskTemp where intQID = @QId and strSSN = @SSN;
                        
                        If @Count = 0
                              UPDATE tblPermTask SET dtCompleted = getdate(), strClosedBy = 'SYSTEM' where intQuestionId = @QId and strSSN = @SSN;
                  Print @Qid
                  Print @SSN
                  FETCH NEXT FROM Find_Close INTO @QId, @SSN;
                  
                  END
                  
            Close Find_Close
            Deallocate Find_Close
            
0
 
LVL 8

Assisted Solution

by:Ghunaima
Ghunaima earned 250 total points
ID: 36500724
This code should also do the update you needed.  
UPDATE tblPermTask
SET    dtCompleted = getdate(), strClosedBy = 'SYSTEM' 
WHERE  intQuestionId + strSSN Not IN (SELECT intQID + strSSN FROM tblPermTaskTemp)
       and intQuestionId in (select intQuestionId from tblSRPQuestion WHERE bitFilterOnly = 1) 
       and and pt.strSSN <> '000000000';

Open in new window

0
 

Author Closing Comment

by:kdeutsch
ID: 36545029
Thanks its working
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now