Ho bisogno di recuperare tutte le righe da una tabella in cui 2 colonne combinate sono tutte diverse. Quindi voglio tutte le vendite che non hanno altre vendite avvenute lo stesso giorno per lo stesso prezzo. Le vendite che sono uniche in base al giorno e al prezzo saranno aggiornate ad uno stato attivo.
Quindi sto pensando:
UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
FROM sales
HAVING count = 1)
Ma mi fa male il cervello ad andare oltre.
SELECT DISTINCT a,b,c FROM t
è poco equivalente a:
SELECT a,b,c FROM t GROUP BY a,b,c
È una buona idea abituarsi alla sintassi GROUP BY, perché è più potente.
Per la tua query, io farei così:
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
)
Se mettete insieme le risposte finora, pulite e migliorate, arrivereste a questa domanda superiore:
UPDATE sales
SET status = 'ACTIVE'
WHERE (saleprice, saledate) IN (
SELECT saleprice, saledate
FROM sales
GROUP BY saleprice, saledate
HAVING count(*) = 1
);
Che è molto più veloce di entrambe. Distrugge le prestazioni della risposta attualmente accettata con un fattore 10 - 15 (nei miei test su PostgreSQL 8.4 e 9.1).
Ma questo è ancora lontano dall'essere ottimale. Usate un NOT EXISTS
(anti-)semi-join per prestazioni ancora migliori. EXISTS
è SQL standard, esiste da sempre (almeno da PostgreSQL 7.2, molto prima che questa domanda fosse posta) e si adatta perfettamente ai requisiti presentati:
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 Vecchio SQL Fiddle
Se non avete una chiave primaria o unica per la tabella (id
nell'esempio), potete sostituirla con la colonna di sistema ctid
per lo scopo di questa query (ma non per altri scopi):
AND s1.ctid <> s.ctid
Ogni tabella dovrebbe avere una chiave primaria. Aggiungine una se non ne hai ancora una. Suggerisco una colonna serial
o una IDENTITY
in Postgres 10+;
Correlato:
La subquery nell'anti-semi-join EXISTS
può smettere di valutare non appena viene trovato il primo duplicato (non ha senso cercare oltre). Per una tabella base con pochi duplicati questo è solo leggermente più efficiente. Con molti duplicati questo diventa molto più efficiente.
Per le righe che hanno già status = 'ACTIVE'
questo aggiornamento non cambierebbe nulla, ma inserirebbe comunque una nuova versione di riga a costo pieno (si applicano piccole eccezioni). Normalmente, non si vuole questo. Aggiungete un'altra condizione WHERE
come dimostrato sopra per evitarlo e renderlo ancora più veloce:
Se status
è definito NOT NULL
, si può semplificare a:
AND status <> 'ACTIVE';
Questa query (a differenza della risposta attualmente accettata da Joel) non tratta i valori NULL come uguali. Le due righe seguenti per (saleprice, saledate)
si qualificherebbero come "distinte" (anche se sembrano identiche all'occhio umano):
(123, NULL)
(123, NULL)
Passa anche in un indice unico e quasi ovunque, dato che i valori NULL non si confrontano uguali secondo lo standard SQL. Vedere:
Invece, GROUP BY
, DISTINCT
o DISTINCT ON ()
trattano i valori NULL come uguali. Usate uno stile di query appropriato a seconda di ciò che volete ottenere. Potete ancora usare questa query più veloce con IS NOT DISTINCT FROM
invece di =
per qualsiasi o tutti i confronti per rendere uguali i NULL. Di più:
Se tutte le colonne che vengono confrontate sono definite NOT NULL
, non c'è spazio per il disaccordo.
Il problema con la tua query è che quando usi una clausola GROUP BY (che essenzialmente fai usando distinct) puoi usare solo colonne che raggruppi per o funzioni aggregate. Non puoi usare la colonna id perché ci sono potenzialmente diversi valori. Nel tuo caso c'è sempre un solo valore a causa della clausola HAVING, ma la maggior parte degli RDBMS non è abbastanza intelligente da riconoscerlo.
Questo dovrebbe funzionare comunque (e non ha bisogno di un join):
UPDATE sales
SET status='ACTIVE'
WHERE id IN (
SELECT MIN(id) FROM sales
GROUP BY saleprice, saledate
HAVING COUNT(id) = 1
)
Potresti anche usare MAX o AVG invece di MIN, è solo importante usare una funzione che restituisca il valore della colonna se c'è solo una riga corrispondente.