[SOLVED] Teradata Failure 7547 Target row updated by multiple source rows

You will get this error code: Failure 7547 Target row updated by multiple source rows 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.

Introduction

Teradata

Teradata is a database system designed to handle large volumes of structured data, such as customer transactions, web logs, and financial data, for data warehousing and analytics purposes. It is known for its scalability, reliability, and high performance in handling high levels of concurrency and data throughput.

Teradata offers various functions and tools for manipulating, analyzing, and visualizing data, and supports SQL, a standard language for accessing and manipulating data in relational databases. It is a popular and powerful choice for data warehousing and business intelligence among many organizations globally.

SQL Update Operation

In SQL, the UPDATE statement is a Data Modification Language (DML) statement that is used to modify the data in existing rows in a table. It is typically used to change the values of one or more columns in a table, based on a specified condition.

In Teradata, the UPDATE statement is used to modify the data in existing rows in a table. The basic syntax for an UPDATE statement is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

In this syntax:

• table_name is the name of the table that you want to update.
• column1, column2, etc. are the names of the columns that you want to update.
• value1, value2, etc. are the new values that you want to assign to the columns.
• condition is an optional condition that specifies which rows to update. If you omit the WHERE clause, all rows in the table will be updated.

It’s important to use caution when using the UPDATE statement, as it can permanently modify the data in your database. It is a good idea to make a backup of your data before running an UPDATE statement, in case you need to restore the original data in case of an error.

Teradata Update Performance Tips

The performance of an UPDATE statement in Teradata can be influenced by a number of factors, including the size of the table, the complexity of the update operation, the distribution of data in the table, and the presence of indexes and other constraints.

Here are some general tips for optimizing The performance of an UPDATE statement in Teradata:

• Use a WHERE clause to specify the rows to update: Updating all rows in a table can be slow, so it is usually more efficient to update only the rows that meet a specific condition.
• Avoid updating large amounts of data: Updating a large number of rows or columns can be slower than updating a smaller amount of data. If possible, try to minimize the number of rows or columns that you are updating.
• Use indexes to locate the rows to update: If you are using a WHERE clause to specify the rows to update, using an index can help the database locate the rows more efficiently.
• Avoid using subqueries in the WHERE clause: Subqueries can be slower to execute than other types of conditions, so it is usually more efficient to avoid using them in the WHERE clause.
• Use the SET rowcount statement to limit the number of rows updated: If you are only interested in updating a certain number of rows, you can use the SET rowcount statement to limit the number of rows that are updated.
• Use the Multiload utility to update large amounts of data: If you need to update a large amount of data, the Multiload utility can be faster than using an UPDATE statement.

By following these tips, you can improve the performance of your UPDATE statements and make them run more efficiently In Teradata.

Failure 7547 Target Row Updated By Multiple Source Rows

The error message “Failure 7547 Target Row Updated By Multiple Source Rows” typically occurs when you are trying to update a row in a target table, but multiple rows in the source table are attempting to update the same target row. This can happen if the join condition between the source and target tables is not specific enough, resulting in multiple source rows being matched to the same target row.

To resolve this error, you will need to modify the join condition between the source and target tables to ensure that only one source row is matched to each target row. This can be done by adding additional conditions to the join clause, or by using a unique identifier (such as a primary key) to match rows between the tables.

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 occurs when you try update CUSTOMER 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 in your instance of Teradata!

It’s also important to ensure that the data in the source and target tables is consistent and accurate, as this can affect the results of the update operation. If there are discrepancies or inconsistencies in the data, you may need to clean and validate the data before attempting the update again.

Solution In Teradata

#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 historical 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;

Summary

Target Row Updated By Multiple Source Rows

This error that appears when trying to update a target table row, but multiple source table rows are attempting to update the same target row. This can occur when the join condition between the source and target tables is not specific enough, leading to multiple source rows being matched to a single target row.

SQL Update Performance Tips

Several factors can impact The performance of an UPDATE statement in Teradata, such as the size of the table, the complexity of the update operation, the distribution of data within the table, and the presence of indexes and other constraints.

By implementing these suggestions, you can enhance the efficiency of your UPDATE statements In Teradata.

Could You Please Share This Post? 
I appreciate It And Thank YOU! :)
Have A Nice Day!