none
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? 

    Here are my DDL layouts???

    CREATE TABLE #TempTableSP1

          ( S1_AccountNumber bigint primary key

           ,S1_FamilyId bigint   

           ,S1_OtherSpouse bigint)

         

    INSERT #TempTableSP1 values

          (123456, 66778, 223456)

    CREATE TABLE A01.AccountMaster

          ( AccountNumber bigint primary key

           ,FamilyID Bigint

           ,FamilyMemberType varchar(2)

           ,Title varchar(20)

           ,FirstName varchar (20)

           ,MiddleName varchar (20)

           ,LastName varchar (20)

           ,Suffix varchar (20)        

         

    INSERT A01.AccountMaster values

          (123456, 66778, ‘F ’, ‘Mr & Mrs’, ‘John’, ‘E’, ‘Smith’, ‘ ‘ ),

          (123456, 66778, ‘SP’, ‘Mr’, ‘John’, ‘E’, ‘Smith’, ‘ ‘),

          (223456, 66778, ‘SP’, ‘Mrs’, ‘Helen’, ‘ ‘, ‘Smith’, ‘ ‘)   

    Here is my SQL Code:

    UPDATE      A01_AccountMaster

    SET Title         = A012.Title,

          FirstName   = A012.FirstName,

          MiddleName  = A012.MiddleName,

          Suffix      = A012.Suffix

         

    FROM  #TempTableSP1 SP1

                      INNER JOIN DS_PROTO.dbo.A01_AccountMaster A011 with (NOLOCK) on A011.AccountNumber = SP1.S1_AccountNumber

                      INNER JOIN DS_PROTO.dbo.A01_AccountMaster A012 with (NOLOCK) on A012.AccountNumber = SP1.S1_OtherSpouse

    If I'm not submitting this correctly, please let me know what I'm doing wrong so I can get it right. Thank you.

    Monday, October 14, 2013 8:38 PM

Answers

  • Please try this:

    UPDATE      A011
    SET Title         = A012.Title,
          FirstName   = A012.FirstName,
          MiddleName  = A012.MiddleName,
          Suffix      = A012.Suffix
         
    FROM  #TempTableSP1 SP1
                      INNER JOIN DS_PROTO.dbo.A01_AccountMaster A011 with (NOLOCK) on A011.AccountNumber = SP1.S1_AccountNumber
                      INNER JOIN DS_PROTO.dbo.A01_AccountMaster A012 with (NOLOCK) on A012.AccountNumber = SP1.S1_OtherSpouse


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Monday, October 14, 2013 9:21 PM

All replies

  • Please try this:

    UPDATE      A011
    SET Title         = A012.Title,
          FirstName   = A012.FirstName,
          MiddleName  = A012.MiddleName,
          Suffix      = A012.Suffix
         
    FROM  #TempTableSP1 SP1
                      INNER JOIN DS_PROTO.dbo.A01_AccountMaster A011 with (NOLOCK) on A011.AccountNumber = SP1.S1_AccountNumber
                      INNER JOIN DS_PROTO.dbo.A01_AccountMaster A012 with (NOLOCK) on A012.AccountNumber = SP1.S1_OtherSpouse


    Saeid Hasani, sqldevelop.wordpress.com

    Download Books Online for SQL Server 2012

    Monday, October 14, 2013 9:21 PM
  • 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.


    Jason

    Monday, October 14, 2013 9:24 PM
  • Jason, it was a typo. The family record should have been:

        (66778, 66778, ‘F ’, ‘Mr & Mrs’, ‘John’, ‘E’, ‘Smith’, ‘ ‘ ),

    Monday, October 14, 2013 9:36 PM
  • Hi Miles,

    You DDL/DML is not correct. Insert into A01.AccountMaster fails because of primary key violation (key column AccountNumber).


    HTH, Regards, Dean Savović, www.comminus.hr/en/

    Tuesday, October 15, 2013 8:18 AM
  • It's the simple things that get me. Thank you for your response and answer. I greatly appreciate it.
    Tuesday, October 15, 2013 11:35 AM