|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/9/2006 5:11:00 AM
Posts: 4,
Visits: 1
|
|
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!
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 8:47:37 PM
Posts: 283,
Visits: 989
|
|
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';
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 6/9/2006 5:11:00 AM
Posts: 4,
Visits: 1
|
|
Thanks, Jim Chapman!
It's work, but runs slowly! Maybe due to using distinct.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 8:47:37 PM
Posts: 283,
Visits: 989
|
|
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);
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/20/2008 5:30:17 AM
Posts: 10,
Visits: 5
|
|
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';
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 5/2/2008 2:28:55 AM
Posts: 15,
Visits: 2
|
|
|
According to ANSI semantics, we can not update the same target row by multiple source rows. This error is to prevent that.
|
|
|
|