oracle 运行合并到查询时出现 ORA-30926 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4077225/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-30926 error when running the merge into query
提问by Ramkumar
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)
);
回答by Peter Lang
Have a look at the answer to ORA-30926: unable to get a stable set of rows in the source tables:
看看对ORA-30926的回答：无法在源表中获得一组稳定的行：
This is usually caused by duplicates in the query specified in USING clause.
...
这通常是由 USING 子句中指定的查询中的重复项引起的。
...
Since my comment seems to have solved your problem, I posted it as answer.
由于我的评论似乎解决了您的问题，因此我将其发布为答案。
回答by Ramkumar
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;
