Declaración de actualización con subconsulta correlacionada: la subconsulta de una sola fila devuelve más de una fila.
Entorno: Oracle 19
Estas son mis dos tablas:
MainTab (g_year, g_prd, match_col, desc)
MainTab está particionada en rango por g_year y subparticionada por g_prd
LookupTab (match_col, desc)
Quiero actualizar la columna desc de MainTab utilizando LookupTab. Estoy ejecutando la siguiente consulta de actualización en una sola subpartición.
update MainTab a
SET a.desc = (SELECT distinct b.desc FROM LookupTab b
where a.g_year =2005 and a.g_prd = 1 and a.desc is null
and a.match_col = b.match_col and b.desc is not null )
where a.g_year =2005 and a.g_prd = 1 and a.desc is null;
Esta consulta arroja “ORA-01427 – single-row subquery returns more than one row” a pesar de tener la cláusula distinct en la subconsulta.
Pregunta: ¿Por qué la cláusula distinct y la cláusula adicional where b.desc is not null no pueden evitar que se devuelvan múltiples filas en la subconsulta?
Podría solucionar esto utilizando “fetch first 1 row only”, pero esperaba que la cláusula distinct también funcionara.
davy.ai
La razón por la cual la cláusula distinct y la cláusula adicional where “b.desc is not null” no son capaces de evitar que se devuelvan múltiples filas en la subconsulta se debe a que estas cláusulas solo se aplican a la columna “b.desc” en LookupTab. No se aplican al conjunto completo de resultados devueltos por la subconsulta, que potencialmente podría tener múltiples filas para un mismo valor de match_col.
En otras palabras, la cláusula distinct solo elimina los valores duplicados en la columna “b.desc”, pero aún podrían existir múltiples filas con diferentes valores de “b.desc” para el mismo valor de “match_col”. De manera similar, la cláusula adicional where “b.desc is not null” solo filtra las filas donde “b.desc” es nulo, pero aún podrían existir múltiples valores no nulos para el mismo valor de “match_col”.
Para asegurarse de que solo se devuelva una sola fila en la subconsulta, se podría utilizar la cláusula “fetch first 1 row only”, como mencionaste. Otra opción podría ser utilizar una cláusula group by en la subconsulta para agrupar los resultados por “match_col” y seleccionar el primer valor no nulo de cada grupo utilizando una función de agregación como “min” o “max”.