Updating the first row of duplicate records

[kokolet]

Hi,

Please I have two tables -say Table A and Table B.

Table A Table B

ID userno ticketno Status userno ticketno Status
1 1211 YAB 1211 YAB LIVE

2 1211 YAB 1232 ACE LIVE

3 1232 ACE 1232 AAT INVALID

4 1232 ACE

5 1232 ACE

6 1232 AAT

I am trying to write an update query in MS Access that will update only the first record of any custno with the status so that my final table A looks like this:

Table A

ID userno ticketno Status

1 1211 YAB LIVE

2 1211 YAB

3 1232 ACE LIVE

4 1232 ACE

5 1232 ACE

6 1232 AAT INVALID

I would appreciate any help on how to write the query.

Thanks in advance.

Kokolet

 

[ByteMyzer]

You might try the following:

UPDATE [Table A] AS TA1
INNER JOIN [Table B] AS TB ON TA1.userno = TB.userno
                       AND TA1.ticketno = TB.ticketno
SET TA1.Status = TB.Status
WHERE TA1.ID = (
    SELECT MIN(TA2.ID)
    FROM [Table A] AS TA2
    WHERE TA2.userno = TA1.userno
    AND TA2.ticketno = TA1.ticketno);

 

[kokolet]

Thanks. For Table A file with about 40,000 records, should this take a while to update? I have been running the query for a while now and it is yet to finish.

 

[ByteMyzer]

If you have properly indexed fields in both tables, the query should take no longer than 10 seconds to process 40,000 records.

I created a model for the tables/fields you mentioned, with the following indexes:

Table A
-------
Primary Key: field ID
Index1: fields userno, ticketno

Table B
-------
Primary Key: fields userno, ticketno