MERGE INTO DMS_CRC_EVENT_SD_TRANS t1
USING DMS_CRC_JL_EVENT_BASIC t2
ON(
to_char(t2.power_on_time,'yyyy-mm-dd')=to_char(t1.outage_end_time,'yyyy-mm-dd') AND
to_char(t2.power_off_time,'yyyy-mm-dd')=to_char(t1.outage_start_time,'yyyy-mm-dd') AND
t1.trans_code=t2.hangcardno AND t2.crc_trans_id IS NULL
)
when matched then
UPDATE SET t1.sd_outage_start_time=power_off_time,t1.sd_outage_end_time=power_on_time
USING DMS_CRC_JL_EVENT_BASIC t2
ON(
to_char(t2.power_on_time,'yyyy-mm-dd')=to_char(t1.outage_end_time,'yyyy-mm-dd') AND
to_char(t2.power_off_time,'yyyy-mm-dd')=to_char(t1.outage_start_time,'yyyy-mm-dd') AND
t1.trans_code=t2.hangcardno AND t2.crc_trans_id IS NULL
)
when matched then
UPDATE SET t1.sd_outage_start_time=power_off_time,t1.sd_outage_end_time=power_on_time
,t1.is_sd_start_time_correct=2,t1.is_sd_end_time_correct=2;
UPDATE DMS_CRC_EVENT_SD_TRANS t1 SET (t1.sd_outage_start_time,t1.sd_outage_end_time
,t1.is_sd_start_time_correct,t1.is_sd_end_time_correct)=
(SELECT t2.power_off_time,t2.power_on_time,2,2
from DMS_CRC_JL_EVENT_BASIC t2
WHERE t2.hangcardno=t1.trans_code
AND to_char(t2.power_off_time,'yyyy-mm-dd')=to_char(t1.outage_start_time,'yyyy-mm-dd')
AND to_char(t2.power_on_time,'yyyy-mm-dd')=to_char(t1.outage_end_time,'yyyy-mm-dd')
AND t2.crc_trans_id IS NULL) WHERE t1.is_sd_start_time_correct =1;