April 21, 2008 at 11:06 am
TTEST
IDADD1ADD2ADD3
101AA1HA1NY
101AA2HA2NJ
101AA3HA2NJ
101AA2HA2NJ
101AA3HA2NJ
101AA2HA2NJ
102AA23HA2NJ
102AA21HA1NY
102AA22HA2NJ
102AA23HA2NJ
102AA22HA2NJ
102AA23HA2NJ
102AA22HA2NJ
102AA23HA2NJ
TMAIN
IDADD1ADD2ADD3FLAG
101AA11HA1NY
101AA12HA2NJ
101AA13HA2NJ
102AA21HA1NY
102AA22HA2NJ
102AA23HA2NJ
TRESULT
MATCH_COUNT
IN TMAIN THERE WILL BE 5 ADDRESSES FOR ONE 'ID' AND IN TTEST THERE WILL BE 8 ADDRESSES FOR THE SAME 'ID'.
NOW I NEED TO COMPARE BOTH THE TABLES IN SUCH A WAY THAT FOR EACH 'ID' IN TMAIN CHECK WHETHER THE 'ADD1','ADD2','ADD3' MATCHES OR NOT IF MATCHES THEN UPDATE THE FIELD 'FLAG' WITH 'Y' OR 'N' AND PROCEED.
AT THE END WANT THE RESULT HOW MANY MATCHED IN THE 'MATCH_COUNT' IN TRESULT TABLE
EVEN IF ONE ROW MATCHED THEN STOP AND INCREASE THE COUNTS
April 21, 2008 at 11:26 am
I would do this as two SQL statements. The first one is an update statement to set the flag in TMain to 1 or Y for a match in TTest. The second would be to get the sum of the flag out of TMain.
UPDATE TMain
SET Flag = 0 -- To initialize the Flag field
UPDATE tm
SET Flag = Flag + 1
FROM TMain tm
WHERE EXISTS (SELECT NULL FROM TTest where Id = tm.Id
AND Field1 = tm.Field1
AND Field2 = tm.Field2
etc...
AND Field5 = tm.Field5)
SELECT SUM(Flag) AS Matches
FROM TMain
WHERE Flag > 0
Dave Novak
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply