All Forums Database
jufu 4 posts Joined 06/06
05 Jun 2006
why this error was raised? help me!

Hi, all: See the following, please! update TTEMP.TB_MID_NEW_INNET_SUBSCRIBER from PVIEW.VW_NET_GSM_NL_200604 a set Active_Month = 200604 where Phone_No = a.MSISDN and Active_Month is null and substr(cast(START_DATE as format 'YYYYMM'), 1, 6) = '200604'; *** Failure 7547 Target row updated by multiple source rows. Statement# 1, Info =0 *** Total elapsed time was 20 minutes and 11 seconds.The syntax of the sql is correct, so I don't know how to avoid the error!

Jim Chapman 449 posts Joined 09/04
05 Jun 2006

Try this:update TTEMP.TB_MID_NEW_INNET_SUBSCRIBERfrom (select distinct MSISDN from PVIEW.VW_NET_GSM_NL_200604) aset Active_Month = 200604where Phone_No = a.MSISDN and Active_Month is nulland substr(cast(START_DATE as format 'YYYYMM'), 1, 6) = '200604';

jufu 4 posts Joined 06/06
05 Jun 2006

Thanks, Jim Chapman!It's work, but runs slowly! Maybe due to using distinct.

Jim Chapman 449 posts Joined 09/04
06 Jun 2006

A correlated subquery might be faster. Try this:update TTEMP.TB_MID_NEW_INNET_SUBSCRIBERset Active_Month = 200604where Active_Month is nulland substr(cast(START_DATE as format 'YYYYMM'), 1, 6) = '200604'and EXISTS (select 1 from PVIEW.VW_NET_GSM_NL_200604 awhere Phone_No = a.MSISDN);

DEEPU 9 posts Joined 10/05
07 Jun 2006

Just adding to Jim's reply- you can use 'group by' also.'GROUP BY' is faster than 'Distinct'.UPDATE TTEMP.TB_MID_NEW_INNET_SUBSCRIBERFROM (SELECT MSISDN FROM PVIEW.VW_NET_GSM_NL_200604 GROUP BY 1) ASET ACTIVE_MONTH = 200604WHERE PHONE_NO = A.MSISDN AND ACTIVE_MONTH IS NULLAND SUBSTR(CAST(START_DATE AS FORMAT 'YYYYMM'), 1, 6) = '200604';

Phani 15 posts Joined 07/04
16 Jun 2006

According to ANSI semantics, we can not update the same target row by multiple source rows. This error is to prevent that.

ulrich 816 posts Joined 09/09
20 Apr 2012

It looks like that dbapp.temp_table contains two rows for at least some Invoice_Id, Item_Nr combination

check 

select Invoice_Id, Item_Nr, count(*)
from dbapp.temp_table
group by 1,2
having count(*) > 1
;

So you either have to agg the two rows into one or you have to run the two updates.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.