Teradata: UPDATE failed. 7547: Target row updated by multiple source rows-CodePudding

Table1 contains id, date, flag. Table2 contains id, date_from, date_to some other attributes

Attribute flag from Table1 has to be updated with the following logic:

UPDATE t1 from Table1 t1, Table2 t2
SET flag = 1
WHERE t1.id = t2.id and t1.date between t2.date_from and t2.date_to

The result is error 7547: Target row updated by multiple source rows. Obviously, the problem is that Table2 contains intersecting intervals. How to rewrite query to update the flag in this case?

CodePudding user response：

Switch to a Correlated Subquery

UPDATE t1 
SET flag = 1
WHERE EXISTS
 ( SELECT 1
   FROM Table2 as t2 
   where t1.id = t2.id 
     and t1.date between t2.date_from and t2.date_to
 )

Or fix your bad source data :-)

CodePudding user response：

Usually it's when the Update identifies multiple sources (in your example from t2) in the target (t1). Try to make t2.id unique.