Duplicates from different tables

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)

Duplicates from different tables

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-11-21 : 08:09:23

Gustavo writes "Hello people!! I thought I put your bright minds to the test! some puzzle came up at work, we have some duplicates on our database. Let me try to put it out clearly, we have two unlinked tables, one has info from our members and uses IGFAMemberID as a primary key, and the other has info from our users (not always members) based on a memberID criteria. For whatever reasons, for every IGFAMemberID in the members table we have two MemberID records in our users table. On our users table we have only one correct MemberId, the other one we can do without. I am trying to run a query or stored procedure that reads the two MemberIDs from the members table, then compares them to the users table and deletes the one who is not present on the user table. I sort of came with a stored procedure I think I got the logic correct but not the syntax, is this at all possible?

CREATE PROC sp_FindDupes
@p_IGFAMemberId numeric(18,0)
AS
DECLARE
@v_MemberID numeric(18,0),
@v_IGFAMemberID numeric(18,0)
SELECT @v_MemberId = MemberID, @v_IGFAMemberID = IGFAMemberID
FROM webuser.IGFAMembers
WHERE IGFAMemberId = @p_IGFAMemberID
IF @v_MemberID IN webuser.IGFAUsers
PRINT @v_MemberID + ' is not a duplicate'
ELSE IF @v_MemberID NOT IN webuser.IGFAUsers
PRINT @v_MemberID + ' is a duplicate'"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-21 : 08:37:05

nr suggested many methods
Refer this
http://sqlteam.com/forums/topic.asp?TOPIC_ID=6256

Madhivanan

Failing to plan is Planning to fail

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-21 : 08:39:12

quote:
Originally posted by AskSQLTeam

Insted of
IF @v_MemberID IN webuser.IGFAUsers
PRINT @v_MemberID + ' is not a duplicate'
ELSE IF @v_MemberID NOT IN webuser.IGFAUsers
PRINT @v_MemberID + ' is a duplicate'"

Use
if exists(select MemberID from webuser.IGFAMembers where MemberID = @v_MemberID)
Begin
PRINT str(@v_MemberID) + ' is not a duplicate'
End
Else
Begin
PRINT str(@v_MemberID) + ' is a duplicate'"
End

and try.

Surendra

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources