Tengo una tabla de alrededor de 7000 filas, una pequeña muestra de las cuales está a continuación:

ORG_ID     Entity_CLASS_ID     Entity_ID     ORGANISATION_ID     ORGANISATION_ROLE_ID    COLUMN_X
-------------------------------------------------------------------------------------------------
781        3                   998           896                 4                       1          
2098       3                   998           3191                4                       66   
3808       4                   998           3191                4                       6555     
780        3                   997           2402                4                       34234     
3807       4                   997           2061                4                       234    
2097       3                   997           2061                4                       6756   

Notará que para cada Entity_ID, hay varias filas. Para cada Entity_ID, hay dos o más Entity_CLASS_ID s diferentes.

Puede ver que, en algunas circunstancias, dos de las filas para cada Entity_ID coinciden también en ORGANISATION_ID, pero tienen diferentes Entity_Class_ID s:

ORG_ID     Entity_CLASS_ID     Entity_ID     ORGANISATION_ID     ORGANISATION_ROLE_ID    COLUMN_X     
-------------------------------------------------------------------------------------------------
2098       3                   998           3191                4                       66     
3808       4                   998           3191                4                       6555        

Si este es el caso, me gustaría eliminar la fila con la clase de entidad ID = 3.

La tabla ordenada resultante debe ser:

ORG_ID     Entity_CLASS_ID     Entity_ID     ORGANISATION_ID     ORGANISATION_ROLE_ID    COLUMN_X
-------------------------------------------------------------------------------------------------
781        3                   998           896                 4                       1          
3808       4                   998           3191                4                       6555     
780        3                   997           2402                4                       34234     
3807       4                   997           2061                4                       234       

Espero haberlo explicado claramente.

¡Intenté resolver esto en código yo mismo, pero la combinación de coincidencias parciales me impide acercarme a una solución!

Gracias de antemano por cualquier ayuda con esto.

1
M.Jones 19 feb. 2020 a las 19:44

2 respuestas

La mejor respuesta

Podemos intentar usar un CTE para este propósito:

WITH cte AS (
    SELECT *, COUNT(*) OVER (PARTITION BY Entity_ID, ORGANISATION_ID) cnt,
        MAX(Entity_CLASS_ID) OVER (PARTITION BY Entity_ID, ORGANISATION_ID) max_ecid
    FROM yourTable
)

DELETE
FROM cte
WHERE
    Entity_CLASS_ID = 3 AND   -- identifies the duplicate
    cnt = 2 AND               -- must occur in a pair
    max_ecid = 4;             -- the other record must be 4

Aquí hay una demostración en ejecución que muestra que se están identificando los registros correctos para su eliminación:

screen capture from demo below

Demo

2
Tim Biegeleisen 19 feb. 2020 a las 16:55

También puedes hacer esto usando exists:

delete from t
where t.Entity_CLASS_ID = 3 and
      exists (select 1
              from t t2
              where t2.Entity_ID = t.Entity_ID and
                    t2.ORGANISATION_ID = t.ORGANISATION_ID and
                    t2.Entity_CLASS_ID <> 3
             );

Esto parece casi una traducción directa de su formulación de la pregunta.

1
Gordon Linoff 19 feb. 2020 a las 17:11