Necesito recuperar todas las filas de una tabla en la que 2 columnas combinadas son todas diferentes. Por lo tanto, quiero todas las ventas que no tienen otras ventas que ocurrieron en el mismo día por el mismo precio. Las ventas que son únicas basadas en el día y el precio se actualizarán a un estado activo.
Así que estoy pensando:
UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
FROM sales
HAVING count = 1)
Pero me duele el cerebro para ir más lejos.
SELECT DISTINCT a,b,c FROM t
...es equivalente a..:
SELECT a,b,c FROM t GROUP BY a,b,c
Es una buena idea acostumbrarse a la sintaxis GROUP BY, ya que es más potente.
Para su consulta, yo'lo haría así:
UPDATE sales
SET status='ACTIVE'
WHERE id IN
(
SELECT id
FROM sales S
INNER JOIN
(
SELECT saleprice, saledate
FROM sales
GROUP BY saleprice, saledate
HAVING COUNT(*) = 1
) T
ON S.saleprice=T.saleprice AND s.saledate=T.saledate
)
Si juntas las respuestas hasta ahora, las limpias y las mejoras, llegarías a esta consulta superior:
UPDATE sales
SET status = 'ACTIVE'
WHERE (saleprice, saledate) IN (
SELECT saleprice, saledate
FROM sales
GROUP BY saleprice, saledate
HAVING count(*) = 1
);
Que es mucho más rápida que cualquiera de ellas. Supera el rendimiento de la respuesta actualmente aceptada por un factor de 10 a 15 (en mis pruebas en PostgreSQL 8.4 y 9.1).
Pero esto todavía está lejos de ser óptimo. Utilice un NOT EXISTS
(anti-)semi-join para un rendimiento aún mejor. EXISTS` es un SQL estándar, ha existido desde siempre (al menos desde PostgreSQL 7.2, mucho antes de que se hiciera esta pregunta) y se ajusta perfectamente a los requisitos presentados:
UPDATE sales s
SET status = 'ACTIVE'
WHERE NOT EXISTS (
SELECT FROM sales s1 -- SELECT list can be empty for EXISTS
WHERE s.saleprice = s1.saleprice
AND s.saledate = s1.saledate
AND s.id <> s1.id -- except for row itself
)
AND s.status IS DISTINCT FROM 'ACTIVE'; -- avoid empty updates. see below
db<>fiddle here Old SQL Fiddle
Si no tiene una clave primaria o única para la tabla (id
en el ejemplo), puede sustituirla por la columna del sistema ctid
para el propósito de esta consulta (pero no para otros propósitos):
AND s1.ctid <> s.ctid
Toda tabla debe tener una clave primaria. Añade una si aún no la tienes. Sugiero una columna serial
o una IDENTITY
en Postgres 10+.
Relacionado:
La subconsulta en el anti-semi-join EXISTS
puede dejar de evaluarse tan pronto como se encuentre el primer duplicado (no tiene sentido seguir buscando). Para una tabla base con pocos duplicados esto es sólo ligeramente más eficiente. Con muchos duplicados esto se vuelve mucho más eficiente.
Para las filas que ya tienen estado = 'ACTIVO'esta actualización no cambiaría nada, pero aún así insertaría una nueva versión de fila a coste completo (se aplican excepciones menores). Normalmente, usted no quiere esto. Añada otra condición
WHERE` como la demostrada arriba para evitar esto y hacerlo aún más rápido:
Si status
está definido NOT NULL
, puede simplificar a:
AND status <> 'ACTIVE';
Esta consulta (a diferencia de la respuesta actualmente aceptada por Joel) no trata los valores NULL como iguales. Las siguientes dos filas para (saleprice, saledate)
se calificarían como "distintas" (aunque parezcan idénticas al ojo humano):
(123, NULL)
(123, NULL)
También pasa en un índice único y en casi cualquier otro lugar, ya que los valores NULL no se comparan igual según el estándar SQL. Ver:
Por otro lado, GROUP BY
, DISTINCT
o DISTINCT ON ()
tratan los valores NULL como iguales. Utilice un estilo de consulta adecuado en función de lo que quiera conseguir. También puede utilizar esta consulta más rápida con IS NOT DISTINCT FROM
en lugar de =
para cualquiera o todas las comparaciones para que los valores NULL sean iguales. Más:
Si todas las columnas que se comparan están definidas como NOT NULL
, no hay lugar para el desacuerdo.
El problema con su consulta es que cuando se utiliza una cláusula GROUP BY (que esencialmente hace utilizando distinct) sólo puede utilizar las columnas que agrupa por o funciones de agregación. No puedes usar la columna id porque hay valores potencialmente diferentes. En tu caso siempre hay un solo valor debido a la cláusula HAVING, pero la mayoría de los RDBMS no son lo suficientemente inteligentes como para reconocer eso.
Sin embargo, esto debería funcionar (y no necesita un join):
UPDATE sales
SET status='ACTIVE'
WHERE id IN (
SELECT MIN(id) FROM sales
GROUP BY saleprice, saledate
HAVING COUNT(id) = 1
)
También podría utilizar MAX o AVG en lugar de MIN, sólo es importante utilizar una función que devuelva el valor de la columna si sólo hay una fila coincidente.