title: USING DB2 MERGE :: Error Code: -788, SQL State: 21506
body: <p><strong>Requirement:</strong> </p>
  
  <p><strong>Input Tables:</strong> <code>csscustomertest</code> &lt; <code>CUSTOMERCID</code> is PK> , <code>CHILDDETAILS</code> &lt; <code>CUSTOMERCID</code> is FK> </p>
  
  <p><strong>Input:</strong>
  <code>CHILDDETAILS.FIRSTNAME</code>: Mary</p>
  
  <p><strong>Output:</strong> <code>CHILDDETAILS.FIRSTNAME</code>: Mchildfirst0215  </p>
  
  <p>where 0215 is the date of birth (MMDD) from the <code>csscustomertest</code> table</p>
  
  <p>I have used this statement to update <code>CHILDDETAILS</code></p>
  
  <pre><code>merge into CHILDDETAILS 
    using ( 
      select 
        CHILDDETAILS.CUSTOMERCID,
        concat(
          trim(concat(substr(coalesce( CHILDDETAILS.FIRSTNAME,''),1,1), 'childfirst')),
          concat(
            trim(coalesce(substr( cast(csscustomertest.dob as char(10) ), 6,2),'Mx')),
            trim(coalesce(substr( cast(csscustomertest.dob as char(10) ), 9,2),'Dx'))                
          )
        )   as    childfirst     
      FROM 
        csscustomertest 
      inner join 
        CHILDDETAILS  
      on ( csscustomertest.CUSTOMERCID = CHILDDETAILS.CUSTOMERCID )  
    )  CUST 
  on ( CUST.CUSTOMERCID = CHILDDETAILS.CUSTOMERCID )  
  when matched   then 
    update set CHILDDETAILS.FIRSTNAME=CUST.childfirst
  </code></pre>
  
  <p>Got this error:</p>
  
  <blockquote>
    <p>[Error Code: -788, SQL State: 21506]  The same row of target table
    "DB2ADMIN.CHILDDETAILS" was identified more than once for an update,
    delete or insert operation of the MERGE statement.. SQLCODE=-788,
    SQLSTATE=21506, DRIVER=3.57.82</p>
  </blockquote>
  
  <p>Also tried using this query:</p>
  
  <pre><code>UPDATE 
    CHILDDETAILS 
  SET 
    FIRSTNAME=(
      select 
        concat(
          trim(concat(substr(coalesce( CHILDDETAILS.FIRSTNAME,''),1,1), 'childfirst')),
          concat(
            trim(coalesce(substr( cast(csscustomertest.dob as char(10) ), 6,2),'Mx')),
            trim(coalesce( substr( cast(csscustomertest.dob as char(10) ), 9,2),'Dx'))                
          )
        )   as    childfirst   
      FROM 
        csscustomertest 
      inner join 
        CHILDDETAILS  
      on ( csscustomertest.CUSTOMERCID = CHILDDETAILS.CUSTOMERCID ) 
      WHERE 
        CHILDDETAILS.CUSTOMERCID=220833
    ) 
  WHERE  
    CHILDDETAILS.CUSTOMERCID=220833;  
  </code></pre>
  
  <p>Getting a similar error:</p>
  
  <blockquote>
    <p>[Error Code: -811, SQL State: 21000]  The result of a scalar
    fullselect, SELECT INTO statement, or VALUES INTO statement is more
    than one row.. SQLCODE=-811, SQLSTATE=21000, DRIVER=3.57.82</p>
  </blockquote>
  
  <p>I think I cannot update the same table in above query, may be I need to write a cursor. Appreciate any advise.</p>