ORA-30926 error when running the merge into query

Question

I'm getting an error with this query:

 MERGE INTO edw_clctns_equip_dim d
   USING psa_equipment_status_history
      ON ( dw_equipment_id = TO_NUMBER (d.clctns_equip_source_id)
           AND d.active_ind = 'Y')
 WHEN MATCHED THEN
  UPDATE
     SET clctns_initial_status_date =
            (SELECT MIN (effective_date)
               FROM psa_equipment_status_history
              WHERE dw_equip_status_type_id IN (
                                       SELECT dw_equip_status_type_id
                                         FROM psa_equipment_status_type
                                        WHERE equipment_status_code = '100')
                AND current_flag = 'Y'
                AND iud_flag <> 'D'
                AND dw_equipment_id = TO_NUMBER (d.clctns_equip_source_id))

I can use the simple update query, but it is taking way too long. Does the merge statement help?

Update EDW_CLCTNS_EQUIP_DIM d
Set CLCTNS_INITIAL_STATUS_DATE =
( SELECT 
    MIN(EFFECTIVE_DATE)
  FROM PSA_EQUIPMENT_STATUS_HISTORY
  WHERE DW_EQUIP_STATUS_TYPE_ID In
  ( Select DW_EQUIP_STATUS_TYPE_ID
    From PSA_EQUIPMENT_STATUS_TYPE
    Where EQUIPMENT_STATUS_CODE = '100'
  )
  AND CURRENT_FLAG = 'Y' 
  AND IUD_FLAG<>'D'
  AND DW_EQUIPMENT_ID=to_number(d.CLCTNS_EQUIP_SOURCE_ID)
);

Have you checked the answer to ORA-30926: unable to get a stable set of rows in the source tables? — Peter Lang
– Peter Lang, Commented Nov 2, 2010 at 11:47
the psa_equipment_status_history table contained multiple entries hence the error — Ramkumar
– Ramkumar, Commented Nov 2, 2010 at 12:06

Community · Accepted Answer · 2017-05-23 10:26:47Z

2

Have a look at the answer to ORA-30926: unable to get a stable set of rows in the source tables:

This is usually caused by duplicates in the query specified in USING clause.
...

_{Since my comment seems to have solved your problem, I posted it as answer.}

edited May 23, 2017 at 10:26

CommunityBot

11 silver badge

answered Nov 2, 2010 at 12:14

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ramkumar · Accepted Answer · 2010-11-02 12:46:32Z

1

yep sorry for posting the question , i was checking only the target table not the source

merge 
into EDW_CLCTNS_EQUIP_DIM d
using 
(
SELECT 
MIN(EFFECTIVE_DATE) as STATUS_DATE,DW_EQUIPMENT_ID
FROM PSA_EQUIPMENT_STATUS_HISTORY
WHERE DW_EQUIP_STATUS_TYPE_ID  in (select DW_EQUIP_STATUS_TYPE_ID from PSA_EQUIPMENT_STATUS_TYPE where EQUIPMENT_STATUS_CODE = '100')  AND 
CURRENT_FLAG = 'Y' 
AND IUD_FLAG<>'D'
group by DW_EQUIPMENT_ID
) a
on (d.CLCTNS_EQUIP_SOURCE_ID=to_char(a.DW_EQUIPMENT_ID) and d.ACTIVE_IND='Y')
WHEN MATCHED
THEN
UPDATE
SET CLCTNS_INITIAL_STATUS_DATE = a.STATUS_DATE;

answered Nov 2, 2010 at 12:46

Ramkumar

1432 silver badges12 bronze badges

Collectives™ on Stack Overflow

ORA-30926 error when running the merge into query

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related