MySql UPDATE only one of the duplicate records

I have a database of customer information. There are separate rows for billing address and shipping address with a flag signifying which it is; BA, SA. Many of the records are set to BA therefore I have duplicates for each customer. I need to set a duplicate record to SA. I tried this but it updated ALL the records that were duplicate. Instead I want to update only one of the duplicate records;

UPDATE customer1 AS C1 JOIN
(
SELECT Ca.user_id, C2.CID, Ca.address_type FROM
customer1 AS Ca JOIN
customer2 AS C2 ON CC.user_id = C2.CID
GROUP BY Ca.user_id
    HAVING COUNT(*)开发者_如何学运维 > 1
) AS C2a ON
C1.user_id = C2.CID
SET C1.address_type = 'SA'

...
LIMIT 0,1

Note that as others have said, you should never have duplicate rows in your database in the first place - it implies your schema is wrong. Also, you'll get a warning using LIMIT without an ORDER BY

As far as the data base is concerned, duplicate records are indistiguishable.

I think your best option is to add an automatically generated ID (see auto_increment) to each record. Then you can uniquely identify the record you want to update.

The other alternative is to copy the data from one of the duplicates, delete them, insert one record then insert the other with the required change.

You should normalize your table schemas to avoid dupplicate data.

Meaning, I would suggest an "address" table with all the adresses and extend the "customer" table by a field BA_address and SA_address, pointing to these addresses.

If you now need to "duplicate" a record from BA to SA, you just put the same addressID inside the SA field, like in the BA field.