合并查询返回ORA

如果您看到我在两个查询中都使用了distinct，那么行的重复不是问题。

您可能有重复的数据。 与其他列一起使用时

DISTINCT

IdToUpdate

CREATE TABLE #MyTable(IdToUpdate INT, LogSetIdToUpdateTo INT);INSERT INTO #MyTable VALUES (1,1), (1,2), (2,1),(3,1);SELECT DISTINCT IdToUpdate, LogSetIdToUpdateToFROM #MyTable;

**[

](https://data.stackexchange.com/stackoverflow/query/387950)**

你会得到

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 cFROM cteGROUP BY IdToUpdateHAVINg COUNT(*) > 1;

一种可行的方法是使用聚合函数，

(MAX/MIN)

DISTINCT

merge into MyTableusing(  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 thenupdate set LogSetId = LogSetIdToUpdateTo