Can anyone suggest a good approach to this problem?

I need to match transactions from two tables on several columns.  When
I have a match, I need to set a match flag and a match number in each
table.  There can be duplicate transactions in either table.  If I have
two transactions in one table that match three in the other, I want to
set the match flag and match number on two transactions in each table
and leave the third one blank.

Results:

table 1
--------------


col A        col B    match_flag  match_number
1                2             Y           1
1                2             Y            2
5                5             Y           3
5                5

table 2
-----------


col A        col B    match_flag  match_number
1                2             Y            1
1                2             Y            2
1                2
5                5             Y            3

Please post your table structures & expected results along with clear
explanation of what you are trying to do. For details, refer to :
www.aspfaq.com/5006

I have two temp tables loaded with the transactions I want to match
with the other table.  They need to match on mid, tran_date, amount,
and card.  When there's a match, I want to set the match_flag to 'AM'
and set the match_number to a unique number given to each matching
transaction.  It's possible that there can be duplicate transactions,
as in the samples below, where there isn't a corresponding match in the
other table.  If there are three in one table and two in the other that
match, I want to set the match_flag and match_number on two from each
table and leave the third from one table null to match on a possible
future load.  I'll be matching on several thousands of transactions.

Let me know if you need more information.  Thanks.



create table #setl (
mid            char(6)      not null,
load_number    int          not null,
detail_number  int          not null,
batch_number   int          not null,
tran_date      datetime     not null,
amount         decimal(8,2) not null,
card           char(4)      not null,
match_flag     char(4)      null,
match_number   int          null )

Data:
---------------
mid         load_number    detail_number   batch_number
543684       23712              1           877
543684       23712              2           877
543684       23712              3           877

tran_date        amount       card     match_flag   match_number
2005-09-30        .01          4444     null             null
2005-09-30        .01          4444     null             null
2005-09-30        .01          4444     null             null


create table #debit (
mid            char(6)      not null,
load_number    int          not null,
detail_number  int          not null,
tran_date      datetime     not null,
amount         decimal(8,2) not null,
card           char(4)      not null,
match_flag     char(4)      null,
match_number   int          null )

Data:
----------------
mid          load_number    batch_number
543684       658                 1
543684       658                 1

tran_date    amount       card     match_flag   match_number
2005-09-30   .01          4444     null             null
2005-09-30   .01          4444     null             null

desired results:
#setl
-----------
mid          load_number    detail_number   batch_number
543684       23712              1           877
543684       23712              2           877
543684       23712              3           877

tran_date       amount       card     match_flag   match_number
2005-09-30      .01          4444     AM             1
2005-09-30      .01          4444     AM             2
2005-09-30      .01          4444     null           null


#debit
-----------
mid          load_number    batch_number
543684       658                 1
543684       658                 1

tran_date    amount       card     match_flag   match_number
2005-09-30   .01          4444     AM             1
2005-09-30   .01          4444     AM             2

First all, in databases, duplicates nullify logic. In other words, without a
column or set of column to uniquely identify a row in a table, it is
impossible to logically manipulate the data in those tables.

Given the data in your tables, it is impossible to tell if the duplicate
rows represent actually duplicated transactions or simply erroneous entries.
So rather than trying to work with meaningless data, you should consider
eliminating the duplicates in the first place. For starters refer to:
http://support.microsoft.com/kb/139444/en-us

If you are looking for a short term workaround to please your boss, write up
a cursor to loop through the rows and assign the match_flag and match_number
values. However, such a solution will do no good since you will still be
left with redundant data with no keys and constraints.

Popuate field with value from last row
Why query optimizer does not select the index?
Finding the right record
CHARINDEX in CASE Within SELECT Statement
Default Value Calculation