You will get this error code when try update the target table using multiple records from source. It completely does not matter if you use MERGE or direct UPDATE statement. In this post I will show you where is the problem and how to fix it.
Let’s look at the below example:
UPDATE CUSTOMER C FROM ( SELECT CUST_ID, CUST_MAIL FROM ORDER ) O SET MAIL = O.CUST_MAIL WHERE C.ID = O.CUST_ID;
It look good, but only if we not have duplicates for CUST_ID attribute at ORDER table. It means error 7547 always occures when you try update CUTOMER table with multiple rows from ORDER table with the same CUST_ID. CUST_ID must be unique value at below query. Check if you have duplicates at your source table!
#1 – whichever value
If it does not matter which rows from source table is appropriate, you can use DISTINCT statement at your query, e.g.:
UPDATE CUSTOMER C FROM ( SELECT DISTINCT CUST_ID, CUST_MAIL FROM ORDER ) O SET MAIL = O.CUST_MAIL WHERE C.ID = O.CUST_ID;
#2 – current record
However, in most of cases you can be interested in taken only a current version of the record for the specific CUST_ID attribute. It works specially if your source table can consists also a hisorical data. You need to looking for load data attribute at source table, e.g.:
UPDATE CUSTOMER C FROM ( SELECT CUST_ID, CUST_MAIL FROM ( SELECT CUST_ID, CUST_MAIL, RANK() OVER (PARTITION BY CUST_ID ORDER LOAD_DATE DESC) AS RANK_SRC FROM ORDER ) S WHERE S.RANK_SRC=1 ) O SET MAIL = O.CUST_MAIL WHERE C.ID = O.CUST_ID;
If you enjoyed this post please add the comment below or share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!