'Target row updated by multiple source rows

UPDATE tb
      FROM Table1 tb,
      (
        SELECT  oreplace(data, '.', '') AS data FROM Table2 
      ) tb2 
      SET column = 'Y'
      WHERE tb.column1 = tb2 .data OR tb.column2 = tb2 .data 
      OR tb.column3 = tb2 .data OR tb.column4 = tb2 .data OR tb.column5 = tb2 .data
      OR tb.column6 = tb2 .data ... upto tb.column15 = tb2 .data;

the column in Table1 has 'N' as defualt. I'm getting 'Target row updated by multiple source rows' error when i execute this update query. I tried with discint but received the same error as there is no duplicates. Please help.
Thank you!

sqlteradata


Solution 1:[1]

the errormessage says that the join between TB and TB2 is not unique and therefore one row in TB ("Target row") has more than 1 rows in TB2 ("multiple source rows"). - Like a (partial) product join.

Either you check for the duplicate rows by rewriting the UPDATE to an SELECT and qualify by multiple sources - or modify the UPDATE. a) one update per target column b) place the join into the subquery, apply distinct and then update

Horst

Solution 2:[2]

With distinct you check that the whole row is unique. However, there could be duplicates within a column that should only have unique values.

You can use a qualify statement to get only unique values from a column.

SEL DISTINCT * FROM YOUR_TABLE_NAME

QUALIFY ROW_NUMBER() OVER (
    PARTITION BY COL_NAME_WITH_DUPS ORDER BY COL_NAME_WITH_DUPS DESC
) = 1

Explanation

For instance see the table below. All rows are unique but the column ID has duplicate values. If you use an update statement and match on a column with duplicate values (e.g. ID) then the matching algorithm gets confused and you get in teradata error update failed 7547.

-----------
|ID|Name  |
-----------
|1 |Alfred|
-----------
|2 |Ben   |
-----------
|1 |Peter |
-----------

Consequently you need to pick either the row |1 |Alfred| or |1 |Peter |. With qualify you can first sort your rows and than pick the first one.

Source

https://hub.packtpub.com/how-to-prevent-errors-while-using-utilities-for-loading-data-in-teradata/

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 hhoeck
Solution 2 Benjamin Ziepert

Related Questions