Answered by:

How do I update a record from another record in the same table without getting the ambiguous error message??

Question

My A01.AccountMaster has three possible records in it. A family record and two spouse records.
The family ID keeps them together by a unique id. The spouse records are different account numbers. I need to flip the name information in the other spouse record to the primary spouse. My work table #TempTableSP1 contains both the primary account number
and the other spouse account number. How do I replace the primary spouse with the other spouse info without getting the ambiguous error message? My work table could be expanded to contain both sets of names and then the SQL could be easily
done updating the primary donor info straight from the work table. But is there a better or easier way?

I don't comprehend your scenario and I'm also unclear about your reference to an "ambiguous error message". Nevertheless, one obvious problem in your example is that the AccountNumber is defined as the primary key (meaning it must be unique
for every row in the table), yet in your data it is the same for the family and for each spouse. The insert statement fails with this error: Cannot insert duplicate key in object 'A01.AccountMaster'. The duplicate key value is (123456).
Is this the "ambiguous error message" you're referring to? Try a surrogate key (e.g. an identity column) for your primary key, or consider a composite key consisting of FamilyID, FamilyMemberType, and something to uniquely identify the spouse, e.g.
SpouseID. Then they can all have the same account number.

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.