CODEWITHSUNDEEP
sqlmergesql-updatedb2
Vinayak Dornala
Vinayak Dornala

Reputation: 1669

USING DB2 MERGE :: Error Code: -788, SQL State: 21506

Requirement:

Input Tables: csscustomertest < CUSTOMERCID is PK> , CHILDDETAILS < CUSTOMERCID is FK>

Input: CHILDDETAILS.FIRSTNAME: Mary

Output: CHILDDETAILS.FIRSTNAME: Mchildfirst0215

where 0215 is the date of birth (MMDD) from the csscustomertest table

I have used this statement to update CHILDDETAILS

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

Got this error:

[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

Also tried using this query:

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;

Getting a similar error:

[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

I think I cannot update the same table in above query, may be I need to write a cursor. Appreciate any advise.

Upvotes: 0

Views: 18296

Answers (2)

Vinayak Dornala
Vinayak Dornala

Reputation: 1669

Thanks for prompt inputs. I am able to resolve the problem.

Cause of the above issue: One to many relation between csscustomertest and CHILDDETAILS table.

Resolution: Added below clauses in the query.

merge into CHILDDETAILS using ( select CHILDDETAILS.CHILDCID, CHILDDETAILS.CUSTOMERCID,CHILDDETAILS.FIRSTNAME,CHILDDETAILS.LASTNAME, 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 ) CUST on ( CUST.CUSTOMERCID = CHILDDETAILS.CUSTOMERCID and CUST.CHILDCID=CHILDDETAILS.CHILDCID)

when matched AND CUST.CUSTOMERCID=220833 then update set CHILDDETAILS.FIRSTNAME=CUST.childfirst;

Code:

merge into CHILDDETAILS 
   using ( 
           select CHILDDETAILS.CHILDCID, CHILDDETAILS.CUSTOMERCID,CHILDDETAILS.FIRSTNAME,CHILDDETAILS.LASTNAME,
        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
     )  CUST 
 on ( CUST.CUSTOMERCID = CHILDDETAILS.CUSTOMERCID and  CUST.CHILDCID=CHILDDETAILS.CHILDCID)  

when matched  AND CUST.CUSTOMERCID=220833
   then update set CHILDDETAILS.FIRSTNAME=CUST.childfirst;

Upvotes: 0

user1919238
user1919238

Reputation:

First of all, the merge statement is the way to go here.

Your problem is that the source query for the update (the one within using(...)) is returning more than one row with the same CHILDDETAILS.CUSTOMERCID.

Either CHILDDETAILS or csscustomertest has more than one row per customer.

You could simply use DISTINCT on the source query if the duplication is in csscustomertest.

If the duplication is in CHILDDETAILS, you may need to use group by and come up with a rule for which details you select. However, I would think duplicate rows in CHILDDETAILS would probably be a problem with your data that needs to be resolved.

Upvotes: 1

Related Questions