By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,321 Members | 1,909 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,321 IT Pros & Developers. It's quick & easy.

Problem with MERGE statement: SQL0969N Error Code: -788

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
yes, it need a one-to-one mapping.

Feb 22 '06 #3

P: n/a
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

P: n/a
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.