Stuck on Procedure... - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Stuck on Procedure...

Author

Topic

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2005-04-04 : 12:37:02

I am trying to write some edit checks in a DTS package for automation purposes and I may be doing this harder then I need to. I have 2 tables. In both tables is a Social Security number. What I need for the edit check to do is to go through all the SSN records in table 1 and see if they exist in table 2, if they do, then do nothing, otherwise I want to do an update. I tried to build a Stored Proc with the following code

UPDATE Phy_Plnt_Tm_Crd set Edt_Chk_Flag = 'SSN NOT FOUND' WHERE Phy_Plnt_Tm_Crd.SSN != (select distinct SSN from Phy_Plnt_Tm_Crd where Phy_Plnt_Tm_Crd.SSN not in (select Employee.SSN from Employee))

But the problem is that the value returned to the right of the != sign is more then one value. Any ideas on how I can accomplish this job?

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-04 : 12:46:37

Try this one:

UPDATE Phy_Plnt_Tm_Crd
SET Edt_Chk_Flag = 'SSN NOT FOUND'
WHERE NOT EXISTS (SELECT 'X' FROM Employee WHERE
Phy_Plnt_Tm_Crd.SSN = Employee.SSN)

murrayb3024
Yak Posting Veteran

79 Posts

Posted - 2005-04-04 : 12:55:17

That worked. Thank you. But what is the X for?

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-04 : 13:00:01

The 'X' is just a constant that the subquery will return. It could be any constant and it could be any column from the Employee table, but it doesn't matter. What matters is the NOT EXISTS which checks if the subquery returned any rows or not.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-04 : 13:02:07

are both of these tables permanent on your database? if so, you are better off writing a View that dynamically calculates this for you. Otherwise, you'll need to run this update any time things change to keep things in sync.

A View that calculates this on the fly, which of course would never need to be manually updated, would look something like this:

SELECT TC.*, CASE WHEN E.SSN is Null THEN 'SSN Not Found' ELSE 'OK' END as SSNMatch
FROM Phy_Plnt_Tm_Crd TC
LEFT OUTER JOIN Employee E
ON TC.SSN = E.SSN

and using that logic, you can return all rows from the Phy_Plnt_Tm_Crd table that don't have a matching SSN in the EMployees table like this:

SELECT TC.*
FROM Phy_Plnt_Tm_Crd TC
LEFT OUTER JOIN Employee E
ON TC.SSN = E.SSN
WHERE E.SSN is Null

None of this might be applicable to you, but beware of storing rendant data in your DB's that can be calculated quickly and efficiently on the fly using JOINS.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources