Target row updated by multiple source rows

General
Enthusiast

Target row updated by multiple source rows


update T1 


from (Select ipic.invoice_id,ipic.Condition_Type_Cd,ipic.Item_Amt,ipic.item_nr 


      from T2 ipic


      WHERE ipic.Invoice_Id IN (SELECT invoice_id from T1


WHERE Company_Cd='0056'


AND Store_state='LA'


AND Sales_Date BETWEEN '2012-02-05' AND '2012-03-03'


  )


      )IPI


SET 


Surcharge=(CASE WHEN IPI.Condition_Type_Cd IN ('NV','VT') THEN IPI.Item_Amt ELSE 0 END)


,Restocking_Fee=(CASE WHEN IPI.Condition_Type_Cd IN ('ZRSF') THEN IPI.Item_Amt ELSE 0 END)


WHERE


T1.invoice_id=IPI.invoice_id


AND T1.Invoice_Item_Nr = IPI.item_nr ;


This query gives the following error: *** Failure 7547 Target row updated by multiple source rows.

                Statement# 1, Info =0 

 *** Total elapsed time was 3 seconds.

How can it be removed ?

3 REPLIES
Enthusiast

Re: Target row updated by multiple source rows

Hi,

There might be more than one value trying to update the Surcharge/Restocking_Fee

because,

T1.invoice_id=IPI.invoice_id


AND T1.Invoice_Item_Nr = IPI.item_nr


is fetching more than one row.


Try giving sel * from, instead of update in the same query and check the records that have multiple values.

you can also check whether where condition is proper/ end_date is null conditione need to be applied if table is type 2.


Thanks,


Karthik. N

Enthusiast

Re: Target row updated by multiple source rows

Yes, invoice_id and Item_nr is not unique in IPI.

You can run the following to get the duplicates

select sub.invoice_id, sub.item_nr, count(*) Dup

from

(

Select ipic.invoice_id invoice_id

,ipic.Condition_Type_Cd Condition_Type_Cd

,ipic.Item_Amt Item_Amt

,i pic.item_nr item_nr 

from T2 ipic

WHERE ipic.Invoice_Id IN (

SELECT invoice_id 

from T1

WHERE Company_Cd='0056'

AND Store_state='LA'

AND Sales_Date BETWEEN '2012-02-05' 

AND '2012-03-03'

  )

) sub

group by 1,2

having Dup > 1;

Regards...

Enthusiast

Re: Target row updated by multiple source rows

This is the most common error for duplicate resords coming from lower stage or from source...if you dont have the choice to add another column to make it unique...try to apply the qualify function by differiantiating the corruct record using the row number() function...