title: Getting error "msg 8156" on doing a merge with a left join
body: <p>I am doing a merge with a number of left joins and they are using the same key I am using with my on statement, and I am getting from SQL Server:</p>
  
  <blockquote>
    <p>Msg 8156, Level 16, State 1, Procedure sp_CommonLoanData, Line 41<br>
    The column 'LNKEY' was specified multiple times for 'l'.
    my the way the I have tested the select statement and it works and retrieves all of the data.
    Code</p>
  </blockquote>
  
  <pre><code>   /*********************************************************************************
  Author: Herb Williams 
  Date: Oct 08 2014 
  Purpose: This stored procedure builds the CommonLoanData table in CTS from the replicated 
           Empower tables in CTS. It check the  Using Merge below is the process. 
           1. Update if there is a loan number
  
           2. Inserted if there is not a loan number 
  
  *********************************************************************************/
  
  CREATE PROCEDURE [dbo].[sp_CommonLoanData]
  
  ---------------------------------------
  -- I N S E R T  
  ---------------------------------------
  
  AS
  MERGE CommonLoanData AS cl
  USING (Select   l.LNKEY as mainKey, 
                  l.STOREDON,  
                  f.NEWFHACASEDATE,
                  m.ADJUSTED_BASE_RATE,
                  d.DISBDATE, 
                  w.COLLATERALORIGRECVD,
                  w.HEDGEDATE,
                  l.LNKEY,
                  b.BORR_LAST, 
                  b.BORR_FIRST,
                  p.PROPWILLBE,
                  lp.LPLANDESC,
                  pf.REF_PURP,
                  m.AMORTTYPE,
                  lp.NUMPMTS,
                  m.INTRATE,
                  m.LOANAMT,
                  m.LTV,
                  r.HOUSING_RATIO,
                  dd.EW_RETAXES,
                  dd.EW_HAZINS,
                  dd.EW_FLOODINS,
                  a.AUSRESULT,
                  t.REPCREDSCORE,
                  s3.HIGH_PRICED_LOAN,
                  m.PRC_BASE_PTS,`enter 
                  ma.POINTS_ADJ,
                  m.ADJUSTED_BASE_RATE,
                  w.ALL_IN_PRICE,
                  pa.ADJ_DESC,
                  pa.POINTS_ADJ,
                  m.LOCKINDATE,
                  m.LOCKINEXPIRES,
                  c.U_AGENT_WAREHOUSE,
                  i.AGENT_COMPANY,
                  repairs.amt,
                  s3.HIGH_PRICED_LOAN,
                  r.DEBT_RATIO,
                  m.CLTV,
                  l.STOREDON
  
  
                  from [EMPOWER].[LN_DBLOCKS] AS l
      left join [EMPOWER].[LN_MTGTERMS]       AS m  on  (m.LNKEY = l.LNKEY)
      left join [EMPOWER].[LN_MANUAL_PRC_ADJ] AS ma on  (ma.LNKEY = l.LNKEY)
      left join [EMPOWER].[LN_CODES]          AS cd on  (cd.LNKEY = l.LNKEY)
      left join [EMPOWER].[LN_DISBURSEMENTS]  AS d  on  (d.LNKEY = l.LNKEY)
      left join [EMPOWER].[LN_DEEDDATA]       AS dd on  (dd.LNKEY = l.LNKEY)
      left join [EMPOWER].[LN_BORRINFO]       AS b  on  (b.LNKEY = l.LNKEY)
      left join [EMPOWER].[LN_PLANDAT]        AS lp on  (lp.LNKEY = l.LNKEY)
      left join [EMPOWER].[LN_FHAVACLC]       AS f  on  (f.LNKEY = l.LNKEY)
      left join [EMPOWER].[LN_RATIOS]         AS r  on  (r.LNKEY = l.LNKEY)
      left join [EMPOWER].[LN_PROPINFO]       AS p  on  (p.LNKEY = l.LNKEY)
      left join [EMPOWER].[LN_PRICADJ]        AS pa on  (pa.LNKEY = l.LNKEY)
      left join [EMPOWER].[LN_PROPREFI]       AS pf on  (pf.LNKEY = l.LNKEY)
      left join [EMPOWER].[LN_SECTION35]      AS s3 on  (s3.LNKEY = l.LNKEY)
      left join [EMPOWER].[LN_AUS_RESULTS]    AS a  on  (a.LNKEY = l.LNKEY)
      left join [EMPOWER].[LN_CONTACTS]       AS c  on  (c.LNKEY = l.LNKEY)
      left join [EMPOWER].[LN_INVESTORS]      AS i  on  (i.LNKEY = l.LNKEY)
      left join [EMPOWER].[LN_TRANSUMM]       AS t  on  (t.LNKEY = l.LNKEY)
      left join [EMPOWER].[U_LN_WEB_BUILDER]  AS w  on  (w.LNKEY = l.LNKEY)
      LEFT JOIN (SELECT LNKEY, sum(ITEMCOST) amt FROM EMPOWER.LN_REPAIRFUNDS GROUP BY LNKEY) repairs on repairs.LNKEY = m.LNKEY)
  
          AS l
          ON (CommonLoanDataId = mainKey )
          and (l.STOREDON &gt;  cl.LastUpdated)
  ---------------------------------------
  -- M E R G E D
  ---------------------------------------
  
  WHEN MATCHED
      THEN UPDATE 
      SET  cl.BranchMarginAdj               = m.ADJUSTED_BASE_RATE,
           cl.ApplicationDate               = m.APP_DATE,
           cl.FHACaseFileDate               = f.NEWFHACASEDATE,
           cl.FundedDate                    = d.DISBDATE,
           cl.CollateralReceivedDate        = w.COLLATERALORIGRECVD,
           cl.HedgedDate                    = w.HEDGEDATE,
           cl.LoanNumber              = l.LNKEY,
           cl.BorrowerLastName        = b.BORR_LAST, 
           cl.BorrowerFirstName       = b.BORR_FIRST,
           cl.Occupancy               = p.PROPWILLBE,
           cl.PropertyState           = p.PROP_STATE,
           cl.PropertyType            = null,
           cl.Program                 = lp.LPLANDESC,
           cl.DocType                 = null,
           cl.LoanType                = null,
           cl.LoanPurpose             = null,
           cl.RefiPurpose             = pf.REF_PURP,
           cl.LienPosition            = null,
           cl.FinanceMethod           = m.AMORTTYPE,
           cl.Term                    = lp.NUMPMTS,
           cl.NoteRate                = m.INTRATE,
           cl.LoanAmount              = m.LOANAMT,
           cl.Ltv                     = m.LTV,
           cl.Hsg                     = r.HOUSING_RATIO,
           cl.WaivedImpounds          = case when dd.EW_RETAXES = 1 or dd.EW_HAZINS = 1 or dd.EW_FLOODINS = 1 then 1 end,
           cl.AUSType                 = null,
           cl.AUSDecision             = a.AUSRESULT,
           cl.LoanFico                = t.REPCREDSCORE,
           cl.MIType                  = null,
           cl.HPMLFlag                = s3.HIGH_PRICED_LOAN,
           cl.SecondaryNotesCPExceptionNotes =null,
           cl.BondStylePrice          = m.PRC_BASE_PTS,
           cl.ProgramPriceAdjTotal    = ma.POINTS_ADJ,
           cl.AdjustedBasePrice       = m.ADJUSTED_BASE_RATE,
           cl.AllInPrice              = w.ALL_IN_PRICE,
           cl.LLPANameItemized        = pa.ADJ_DESC,
           cl.LLPAAmountItemized      = pa.POINTS_ADJ,
           cl.LockDate                = m.LOCKINDATE,
           cl.LockExpiration          = m.LOCKINEXPIRES,
           cl.LockCanceled            = null,
           cl.CondoType               = null,
           cl.AUSRiskDecision         = null,
           cl.WarehouseLine           = c.U_AGENT_WAREHOUSE,
           cl.Investor                = i.AGENT_COMPANY,
           cl.ExclusionIndicator      = case when repairs.amt &gt; 0 or mcc &gt; 0 or s3.HIGH_PRICED_LOAN &gt; 0 then 1 else 0 end,
           cl.Dti                     = r.DEBT_RATIO,
           cl.Cltv                    = m.CLTV,
           cl.[Source]                = null,
           cl.LastUpdated             = l.STOREDON,
           cl.WireSentDate            = null, 
           cl.WireReturningDate       = null,
           cl.ShippedToInvestorDate   = null,
           cl.InvestorPriceLockDate   = null,
           cl.InvestorPriceLockExpiration = null,
           cl.FirstInvestorPaymentDate = null,
           cl.PAReceivedDate           = null
  
  
  WHEN NOT MATCHED
      THEN INSERT VALUES (
           m.ADJUSTED_BASE_RATE,
           m.APP_DATE,
           f.NEWFHACASEDATE,
           d.DISBDATE,
           w.COLLATERALORIGRECVD,
           w.HEDGEDATE,
           l.LNKEY,
           b.BORR_LAST, 
           b.BORR_FIRST,
           p.PROPWILLBE,
           p.PROP_STATE,
           null,
           lp.LPLANDESC,
           null,
           null,
           null,
           pf.REF_PURP,
           null,
           m.AMORTTYPE,
           lp.NUMPMTS,
           m.INTRATE,
           m.LOANAMT,
           m.LTV,
           r.HOUSING_RATIO,
           case when dd.EW_RETAXES = 1 or dd.EW_HAZINS = 1 or dd.EW_FLOODINS = 1 then 1 end,
           null,
           a.AUSRESULT,
           t.REPCREDSCORE,
           null,
           s3.HIGH_PRICED_LOAN,
           null,
           m.PRC_BASE_PTS,
           ma.POINTS_ADJ,
           m.ADJUSTED_BASE_RATE,
           w.ALL_IN_PRICE,
           pa.ADJ_DESC,
           pa.POINTS_ADJ,
           m.LOCKINDATE,
           m.LOCKINEXPIRES,
           null,
           null,
           null,
           c.U_AGENT_WAREHOUSE,
           i.AGENT_COMPANY,
           case when repairs.amt &gt; 0 or mcc &gt; 0 or s3.HIGH_PRICED_LOAN &gt; 0 then 1 else 0 end,
           r.DEBT_RATIO,
           m.CLTV,
           null,
           l.STOREDON,
           null, 
           null,
           null,
           null,
           null,
           null,
           null);
  
  SELECT  resource_type, request_mode, resource_description
  FROM    sys.dm_tran_locks
  
  SELECT * FROM cl
  ROLLBACK 
  </code></pre>