Update question

I posted a message a while back about updating duplicate rows in a table, but I don't think I explained the scenario very well, so here's version 2.0:

I have a table which can have duplicate rows in it. I need to update these rows with an identifier.

Here's an example of a duplicate row (table_a):

Card_no Amount

12345      20.00
12345      20.00

(2 transactions of 20.00 on card no 12345; total 40.00)

I then need to update a batch and item number in this table with batch and term numbers held in another table (table_b). Eg:

Card_no     Batch_no     Item_no
12345       100          1
12345       100          2

Therefore, I can't write something like this:

UPDATE table_a
SET (table_a.Batch_no, table_a.Item_no) =
(SELECT table_b.Batch_no

,	table_b.Item_no
FROM	table_b
WHERE	table_a.Card_no = table_b.Card_no)

... because it'll update both rows in table_a with the same data:

Card_no Amount Batch_no Item_no

12345      20.00     100         2
12345      20.00     100         2

...rather than assigning different values to each of the 2 rows in table_a:

Card_no Amount Batch_no Item_no

12345      20.00     100         1
12345      20.00     100         2

... which is what I'm trying to do.

I need to do this update without changing the existing table structure, as the system is a "legacy system" and is owned by another company.

Thanks for any assistance,

Benetnasch Received on Fri Jun 08 2001 - 04:09:08 CDT