f



Problem with MERGE statement: SQL0969N Error Code: -788

Hi guys,

I'm getting the following error message when trying to run a MERGE
statement I'm putting together. The syntax looks right to me But i must
be missing something. The "srce" table reference runs fine on its own.

[Error Code: -788, SQL State: S1000]  [IBM][CLI Driver][DB2/6000]
SQL0969N

There is no message text corresponding to SQL error "-788" in the
message file on this workstation.

The error was returned from module "SQLRIERR" with original tokens
"SFDPRFP.TB_FA_ALLOC_HIST_C".

Can anyone spot any glaring errors just through eyeballing this?

 MERGE INTO SFDPRFP.TB_FA_ALLOC_HIST_C target
 USING (

SELECT (CASE WHEN LEFT(driver.ALLOC_TYPE, 6)='Unpaid' THEN
            RTRIM(COALESCE(hist.Alloc_type, driver.ALLOC_TYPE)) ||
   	    CASE WHEN hist.ALLOC_TYPE='PAY Non Ledger Acc' THEN '/' ||
COALESCE(hist.Alloc_Account, '') ELSE '' END
       ELSE
            RTRIM(driver.ALLOC_TYPE) ||
   	    CASE WHEN driver.ALLOC_TYPE='PAY Non Ledger Acc' THEN  '/' ||
COALESCE(driver.Alloc_Account, '') ELSE '' END
       END ) AS merged_value, driver.alloc_type, hist.alloc_type,
driver.alloc_item_id

  FROM SFDPRFP.TB_FA_ALLOC_HIST_C driver
  LEFT OUTER JOIN SFDPRFP.VW_FA_ALLOC_HIST_M hist
    ON driver.FROM_ID = hist.TO_ID
   AND driver.RMT_PYMNT_ID = hist.RMT_PYMNT_ID
   AND driver.ALLOC_ITEM_ID <> hist.ALLOC_ITEM_ID
) AS srce
    ON target.ALLOC_ITEM_ID = srce.ALLOC_ITEM_ID
  WHEN MATCHED THEN
UPDATE SET target.derived_allocation_type = srce.merged_value
;

If I simplify the case, the following works. It's a bit pointless, but
it works.

 MERGE INTO SFDPRFP.TB_FA_ALLOC_HIST_C target
 USING (

SELECT 'test' AS merged_value, driver.alloc_item_id
  FROM SFDPRFP.TB_FA_ALLOC_HIST_C driver

) AS srce
    ON target.ALLOC_ITEM_ID = srce.ALLOC_ITEM_ID
  WHEN MATCHED THEN
UPDATE SET target.derived_allocation_type = srce.merged_value 
;

0
marklawford
2/22/2006 9:03:30 AM
comp.databases.ibm-db2 12448 articles. 3 followers. arunrocks (9) is leader. Post Follow

4 Replies
5190 Views

Similar Articles

[PageSpeed] 48

As a supplementary question, does there need to be a one-to-one mapping
between the target rows to be updated and the rows found in the "srce"
table reference?

I'd assumed that for each row found in the joined table reference, the
matching row in the target table would be updated. If there's more than
one match, it would be updated more than once.

0
marklawford
2/22/2006 9:35:29 AM
yes, it need a one-to-one mapping.

0
Hardy
2/22/2006 10:32:23 AM
In article <1140604343.819920.204750@f14g2000cwb.googlegroups.com>, 
Hardy (wyhang@gmail.com) says...
> yes, it need a one-to-one mapping.
> 
> 

This is the message text of the -788 error code:

SQL0788N The same row of target table "<table-name>" was
          identified more than once for an update, delete or
          insert operation of the MERGE statement.

Explanation:

 The ON search-condition of the MERGE statement matched a single
row from the target table with multiple rows of the source
table-reference. This could result in the target row being
operated on more than once with an update or delete operation and
is not allowed.

 The statement cannot be processed.

User Response:

 Correct the search condition to ensure that each row in the
target table only matches one row in the source table.

 Alternatively, aggregate the rows in the source table that match
the row in the target table by using GROUP BY functions or
cleanse the data by using the ROW_NUMBER() OLAP expression.

 sqlcode :  -788

 sqlstate :  21506

0
Gert
2/22/2006 10:51:34 AM
Gert van der Kooij wrote:
> In article <1140604343.819920.204750@f14g2000cwb.googlegroups.com>, 
> Hardy (wyhang@gmail.com) says...
> 
>>yes, it need a one-to-one mapping.
>>
>>
> 
> 
> This is the message text of the -788 error code:
> 
> SQL0788N The same row of target table "<table-name>" was
>           identified more than once for an update, delete or
>           insert operation of the MERGE statement.
> 
> Explanation:
> 
>  The ON search-condition of the MERGE statement matched a single
> row from the target table with multiple rows of the source
> table-reference. This could result in the target row being
> operated on more than once with an update or delete operation and
> is not allowed.
> 
>  The statement cannot be processed.
> 
> User Response:
> 
>  Correct the search condition to ensure that each row in the
> target table only matches one row in the source table.
> 
>  Alternatively, aggregate the rows in the source table that match
> the row in the target table by using GROUP BY functions or
> cleanse the data by using the ROW_NUMBER() OLAP expression.
> 
>  sqlcode :  -788
> 
>  sqlstate :  21506
> 
Mark,
The reason for this restriction (which is ANSI Standard btw) is that teh 
order in which you woule get these multiple updates is not 
deterministic, so the result is unpredictable.
What you need to do is GROUP BY ALLOC_ITEM_ID
and you must decide how to aggregate the dups.

Note that MERGE's "little brother" behaves teh same way:
UPDATE target SET = (SELECT .. FROM source WHERE ..)
WHERE EXISTSS(SELECT 1 FROM source WHERE ...)
Here the row select in teh SET clause complains if more than one row 
qualified in SOURCE.

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
0
Serge
2/22/2006 12:44:40 PM
Reply: