mercredi 21 octobre 2015

SQL 2005 - Updating table in a one to many relationship

I have a staging table that was created from a flat file and modified before copying to a final destination. Some of the records will be inserted and the rest updated if needed. The only issue I have is it is a one to many relationship. The table is a list of retailers and some of them are entered with the same store name and SS# more than once but with a different contact type. Such as:

Store_ID   SS#         First_Name     Last_Name        Type  Description
________________________________________________________________________
1234       123-12-1234 JP             Crawford         A     Owner
1234       123-12-1234 JP             Crawford         D     Other Contact 1
1234       987-76-9876 Aaron          Nola             E     Other Contact 2
1236       321-12-3210 Mikael         Franco           A     Manager
1236       321-12-3210 Mikael         Franco           J     Other Contact 7

I need to be able to select one of the records when there is a duplicate store_id/SS#. There is no date available so I do not know which record was added last. In cases where one of the records is "Owner" and the other is "Other Contact" I can assume the correct one is "Owner". Same for if one of them is "Manager" for instance. But there are some examples where one record may be "Other Contact 5" and the next is "Other Contact 6".

Any suggestions are greatly appreciated.

Aucun commentaire:

Enregistrer un commentaire