sql - 运行合并到查询时出现 ORA-30926 错误

Question

此查询出现错误:

 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))

我可以使用简单的更新查询，但它花费的时间太长。合并语句有帮助吗？

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)
);

Answer 1

看看对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.
...

由于我的评论似乎解决了您的问题，因此我将其发布为答案。