why this error was raised? help me!

Database
Fan

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!
6 REPLIES
Enthusiast

Re: why this error was raised? help me!

Try this:

update TTEMP.TB_MID_NEW_INNET_SUBSCRIBER
from (select distinct MSISDN 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';
Fan

Re: why this error was raised? help me!

Thanks, Jim Chapman!

It's work, but runs slowly! Maybe due to using distinct.
Enthusiast

Re: why this error was raised? help me!

A correlated subquery might be faster. Try this:

update TTEMP.TB_MID_NEW_INNET_SUBSCRIBER
set Active_Month = 200604
where Active_Month is null
and substr(cast(START_DATE as format 'YYYYMM'), 1, 6) = '200604'
and EXISTS
(select 1 from PVIEW.VW_NET_GSM_NL_200604 a
where Phone_No = a.MSISDN);
Enthusiast

Re: why this error was raised? help me!

Just adding to Jim's reply- you can use 'group by' also.
'GROUP BY' is faster than 'Distinct'.

UPDATE TTEMP.TB_MID_NEW_INNET_SUBSCRIBER
FROM (SELECT MSISDN FROM PVIEW.VW_NET_GSM_NL_200604 GROUP BY 1) 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';
Enthusiast

Re: why this error was raised? help me!

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

Re: why this error was raised? help me!

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.