470,810 Members | 866 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,810 developers. It's quick & easy.

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
;

Feb 22 '06 #1
4 17476
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.

Feb 22 '06 #2
yes, it need a one-to-one mapping.

Feb 22 '06 #3
In article <11**********************@f14g2000cwb.googlegroups .com>,
Hardy (wy****@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

Feb 22 '06 #4
Gert van der Kooij wrote:
In article <11**********************@f14g2000cwb.googlegroups .com>,
Hardy (wy****@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
Feb 22 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Ray | last post: by
1 post views Thread by Bob Stearns | last post: by
8 posts views Thread by Squirrel | last post: by
3 posts views Thread by Bob Stearns | last post: by
5 posts views Thread by melentina | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.