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

Merge stmt

P: n/a
I have query like this..

MERGE INTO POL_INT_NAME_V A
USING (
SELECT DISTINCT(A.INT_NAME_ID),
CASE
WHEN A.BUS_LICENSE_NBR IS NOT NULL THEN
SUBSTR(A.BUS_LICENSE_NBR,1,11)
WHEN RTRIM(A.BUS_LICENSE_NBR)!='' THEN
SUBSTR(A.BUS_LICENSE_NBR,1,11)
ELSE a.TAX_ID
END AS TAX_ID,

CASE
WHEN a.BUS_LICENSE_NBR IS NOT NULL THEN 'FEIN'
WHEN RTRIM(a.BUS_LICENSE_NBR)!='' THEN 'FEIN'
ELSE 'TAX_ID'
END AS TAX_ID_TYPE

FROM POL_INT_NAME_MULTIPLE_V AS B,POL_INT_NAME_V A
WHERE A.INT_NAME_ID=B.INT_NAME_ID
AND B.PRIMARY_NAME_F='Y'
AND B.NAME_BUSINESS IS NOT NULL
AND B.NAME_BUSINESS != '') AS C

ON A.INT_NAME_ID=C.INT_NAME_ID
WHEN MATCHED THEN
UPDATE SET A.TAX_ID=C.TAX_ID,A.TAX_ID_TYPE=C.TAX_ID_TYPE;
and it is giving an error ..

SQL0788N The same row of target table "DB2ADMIN.POL_INT_NAME_V" was
identified more than once for an update, delete or insert operation of
the
MERGE statement. SQLSTATE=21506
can I re write the query ..any thoughts..

Thanks

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
I guess that table POL_INT_NAME_V have more than one row which have
same INT_NAME_ID.
If so, removing DISTINCT and adding more conditions to ON clause(if
neccesary, also to WHERE clause and/or SELECT list) to identify one
update row of POL_INT_NAME_V for each selected row in USING might
resolve your problem.

Nov 12 '05 #2

P: n/a
I'm sorry my poor english.
My previous explanation might be not enough and may give you some
confusion.

DISTINCT is not appled to a column(nor some columns). It is applied to
whole rows.
You wrote "DISTINCT(A.INT_NAME_ID)". But, this parentheses are no use.
DISTINCT eliminate duplications of same value combination of
(INT_NAME_ID, TAX_ID, TAX_ID_TYPE).

So, my guess is as followings:
Your SELECT staatement in USING clause must return some rows which have
same INT_NAME_ID value,
but TAX_ID and/or TAX_ID_TYPE are different.
And, you specify only A.INT_NAME_ID=C.INT_NAME_ID in ON clause.
As a result, such rows in POL_INT_NAME_V A will be attempted to updated
multiple times by rows which have same C.INT_NAME_ID.

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.