CODEWITHSUNDEEP
db2sql-merge
Filozof
Filozof

Reputation: 3

DB2 - SQL - MERGE - An unexpected token -

I am a beginner here so soryy for mistake :-(

I try to execute Marge but unfortunately keeps getting the same error and I don't know what I'm doing wrong.

MERGE INTO TB_CLIENT_ACCOUNT_LINK as A

    USING

        (SELECT client_id, account_id, PROPER_NAME
           FROM TB_CLIENT_ACCOUNT_LINK
          WHERE client_id=233176) as B

        ON (A.account_id = B.account_id)

        WHEN MATCHED THEN

        UPDATE
           SET A.PROPER_NAME = B.PROPER_NAME
         WHERE A.client_id=110966
           AND A.PROPER_NAME IS NOT NULL

And I had error:

An unexpected token "where" was found following "NAME = B.PROPER_NAME". Expected tokens may include: "OVERLAPS".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.72.52 SQL Code: -104, SQL State: 42601

What do I want to do?

I want to copy PROPER_NAME from one client to another that has the same ACCOUNT_ID.

I don't want to do it this way, I want to use marge or (if possible) the advanced update option.

I normally do it like this

update TB_CLIENT_ACCOUNT_LINK set PROPER_NAME = 'NAME1'
 where A.client_id=110966 and A.PROPER_NAME is not null;
update TB_CLIENT_ACCOUNT_LINK set PROPER_NAME = 'NAME2'
 where A.client_id=110966 and A.PROPER_NAME is not null;
update TB_CLIENT_ACCOUNT_LINK set PROPER_NAME = 'NAME3'
 where A.client_id=110966 and A.PROPER_NAME is not null;

etc.

Upvotes: 0

Views: 728

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12314

Refer to the MERGE statement syntax.
You have a number of mistakes in your statement.

  • It's not possible to specify WHERE clause in the update-operation
  • The assignment-clause can't contain a correlation name at the left side

Use one of the following instead.

MERGE INTO TB_CLIENT_ACCOUNT_LINK as A
USING
(select client_id,account_id,PROPER_NAME from TB_CLIENT_ACCOUNT_LINK where client_id=233176) as B
ON (A.account_id = B.account_id)
--AND A.client_id=110966 and A.PROPER_NAME is not null
when matched
-- Comment out the following line, if you uncommented the one above
AND A.client_id=110966 and A.PROPER_NAME is not null
then update set PROPER_NAME = B.PROPER_NAME

Upvotes: 1

Related Questions