473,320 Members | 1,858 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 18530
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Ray | last post by:
I have a list of about 20,000 rows that I am updating. I loop through each row in my program and basically do the following (1) select * from TABLE where SID= for update /*lock the row*/ (2)...
1
by: Bob Stearns | last post by:
Does the following message get generated before or after the additional conditions given in the merge are evaluated? Error: The same row of target table "NULLID.AICA" was identified more than...
8
by: Squirrel | last post by:
Hi everyone, I've created a mail merge Word doc. (using Office XP) , the data source is an Access query. Functionality I'm attempting to set up is: User sets a boolean field to true for...
2
by: Ray Todd Jr | last post by:
Could someone please look at this code and tell me what I am missing, please? The problem that is occuring is I am given an error (5922) that states "Word was unable to open the data source" As...
11
by: Bruce D | last post by:
I have a DataTable that I need to merge the 'lastname' and 'firstname' fields to be displayed in a datagrid. I know I can use the SQL statement to merge the fields into one, but I want to know if...
1
by: rebeaj | last post by:
Merge SQL statement will not compile. Can anyone help me out? Create Or Replace Procedure P_pvt_merge As Begin Merge Into Employees2 B Using ( Select Employee_id, First_name, Last_name,...
3
by: Bob Stearns | last post by:
We have a very poorly performing MERGE statement (an hour or more on tables of ~10000 and ~100000). This may require building temporary tables with appropriate indexes, but I thought I would ask...
5
by: melentina | last post by:
Hello, I'm trying to use MERGE statement in a procedure, please see the code below: procedure gen_nou (p_data date) is v_atribut_id number; begin v_atribut_id=100;
1
by: sumanroyc | last post by:
Hi All, We are encountering a strange problem with the merge command. The following statement works :- merge into ATTRIBUTE_GROUP@US_PRODUCT_UAT a using ( select
1
by: UDBDBA | last post by:
Hi, If someone can clarify this, it would be of great help! We have a merge, which is written *only* to update the target table. but the access plan shows branches with inserts and deletes on...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.