SQL 返回 ORA-30926 的合并查询：无法在源表中获得一组稳定的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33627802/
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
Merge query returning ORA-30926: unable to get a stable set of rows in the source tables
提问by Anamay
I have two merge queries which triggers one after another
我有两个合并查询，它们一个接一个地触发
First Query
第一个查询
merge into MyTable
using
(
select distinct nullLogSetId.Id as IdToUpdate,
knownLogSetId.LogSetId LogSetIdToUpdateTo
from MyTable knownLogSetId
join MyTable nullLogSetId
on knownLogSetId.IdentifierType = nullLogSetId.IdentifierType
and knownLogSetId.Identifier = nullLogSetId.Identifier
where
knownLogSetId.IdentifierType = 'ABC'
and knownLogSetId.LogSetId >= 0
and nullLogSetId.LogSetId = -1
)
on (Id = IdToUpdate)
when matched then
update set LogSetId = LogSetIdToUpdateTo
Second Query
第二次查询
merge into MyTable
using
(
select distinct nullLogSetId.Id as IdToUpdate,
knownLogSetId.LogSetId LogSetIdToUpdateTo
from MyTable knownLogSetId
join MyTable nullLogSetId
on knownLogSetId.IdentifierType = nullLogSetId.IdentifierType
and knownLogSetId.Identifier = nullLogSetId.Identifier
where
knownLogSetId.IdentifierType = 'DEF'
and knownLogSetId.LogSetId >= 0
and nullLogSetId.LogSetId = -1
) on (Id = IdToUpdate)
when matched then
update set LogSetId = LogSetIdToUpdateTo
I am calling these queries from .NET one after another using OracleCommand
我正在使用 OracleCommand 一个接一个地从 .NET 调用这些查询
The first works fine but when second gets triggered i am getting error
第一个工作正常但是当第二个被触发时我收到错误
ORA-30926: unable to get a stable set of rows in the source tables
ORA-30926: 无法在源表中获得一组稳定的行
I have read all the relevent questions and tried from my side as well , if you see i used distinct in both of the queries so duplicating of rows is not an issue. Can please anybody help me out what i am i doing wrong , it could be a basic stuff as i am new to queries , please help me out
我已经阅读了所有相关问题并从我这边进行了尝试，如果您看到我在两个查询中都使用了 distinct，那么行的重复不是问题。请任何人帮我解决我做错了什么，这可能是一个基本的东西，因为我不熟悉查询，请帮助我
回答by Lukasz Szozda
if you see i used distinct in both of the queries so duplicating of rows is not an issue.
如果您看到我在两个查询中都使用了 distinct，因此行重复不是问题。
You probably have duplicates in data. DISTINCTdoes not guarantee you have IdToUpdateunique when you use it with other columns.See:
您可能有数据重复。当您将它与其他列一起使用时DISTINCT，不保证您具有IdToUpdate唯一性。看：
CREATE TABLE #MyTable(IdToUpdate INT, LogSetIdToUpdateTo INT);
INSERT INTO #MyTable VALUES (1,1), (1,2), (2,1),(3,1);
SELECT DISTINCT IdToUpdate, LogSetIdToUpdateTo
FROM #MyTable;
You will get IdToUpdatetwice. Check your data:
你会得到IdToUpdate两次。检查您的数据：
with cte AS (
select distinct nullLogSetId.Id as IdToUpdate,
knownLogSetId.LogSetId LogSetIdToUpdateTo
from MyTable knownLogSetId
join MyTable nullLogSetId
on knownLogSetId.IdentifierType = nullLogSetId.IdentifierType
and knownLogSetId.Identifier = nullLogSetId.Identifier
where
knownLogSetId.IdentifierType = 'DEF'
and knownLogSetId.LogSetId >= 0
and nullLogSetId.LogSetId = -1
)
SELECT IdToUpdate, COUNT(*) AS c
FROM cte
GROUP BY IdToUpdate
HAVING COUNT(*) > 1;
One way to go is to use aggregation function(MAX/MIN)instead of DISTINCT:
一种方法是使用聚合函数(MAX/MIN)而不是DISTINCT：
merge into MyTable
using
(
select nullLogSetId.Id as IdToUpdate,
MAX(knownLogSetId.LogSetId) AS LogSetIdToUpdateTo
from MyTable knownLogSetId
join MyTable nullLogSetId
on knownLogSetId.IdentifierType = nullLogSetId.IdentifierType
and knownLogSetId.Identifier = nullLogSetId.Identifier
where
knownLogSetId.IdentifierType = 'DEF'
and knownLogSetId.LogSetId >= 0
and nullLogSetId.LogSetId = -1
GROUP BY nullLogSetId.Id
) on (Id = IdToUpdate)
when matched then
update set LogSetId = LogSetIdToUpdateTo
