Ik moet alle rijen ophalen uit een tabel waar 2 kolommen gecombineerd allemaal verschillend zijn. Dus ik wil alle verkopen die geen andere verkopen hebben die op dezelfde dag hebben plaatsgevonden voor dezelfde prijs. De verkopen die uniek zijn op basis van dag en prijs zullen worden bijgewerkt naar een actieve status.
Dus ik'ben aan het denken:
UPDATE sales
SET status = 'ACTIVE'
WHERE id IN (SELECT DISTINCT (saleprice, saledate), id, count(id)
FROM sales
HAVING count = 1)
Maar mijn hersenen doen pijn om verder te gaan dan dat.
SELECT DISTINCT a,b,c FROM t
is eigenlijk gelijk aan:
SELECT a,b,c FROM t GROUP BY a,b,c
Het is een goed idee om te wennen aan de GROUP BY syntaxis, omdat die krachtiger is.
Voor jouw query, zou ik het als volgt doen:
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
)
Als je de antwoorden tot nu toe samenvoegt, opruimt en verbetert, kom je tot deze superieure vraag:
UPDATE sales
SET status = 'ACTIVE'
WHERE (saleprice, saledate) IN (
SELECT saleprice, saledate
FROM sales
GROUP BY saleprice, saledate
HAVING count(*) = 1
);
Die veel sneller is dan elk van hen. Verneukt de prestatie van het huidige geaccepteerde antwoord met factor 10 - 15 (in mijn tests op PostgreSQL 8.4 en 9.1).
Maar dit is nog steeds verre van optimaal. Gebruik een NOT EXISTS
(anti-)semi-join voor nog betere prestaties. EXISTS
is standaard SQL, bestaat al sinds mensenheugenis (in ieder geval sinds PostgreSQL 7.2, lang voordat deze vraag werd gesteld) en voldoet perfect aan de gestelde eisen:
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
Als je geen primaire of unieke sleutel hebt voor de tabel (id
in het voorbeeld), kun je dit vervangen door de systeem kolom ctid
voor het doel van deze query (maar niet voor sommige andere doeleinden):
AND s1.ctid <> s.ctid
Elke tabel zou een primaire sleutel moeten hebben. Voeg er een toe als je er nog geen hebt. Ik stel een serial
of een IDENTITY
kolom voor in Postgres 10+.
Gerelateerd:
De subquery in de EXISTS
anti-semi-join kan stoppen met evalueren zodra de eerste dupe is gevonden (geen zin om verder te zoeken). Voor een basistabel met weinig duplicaten is dit maar een klein beetje efficiënter. Met veel doublures wordt dit weg efficiënter.
Voor rijen die al status = 'ACTIVE'
hebben zou deze update niets veranderen, maar toch een nieuwe rijversie invoegen tegen volledige kostprijs (kleine uitzonderingen zijn van toepassing). Normaal gesproken, wil je dit niet. Voeg een andere WAAR
voorwaarde toe zoals hierboven gedemonstreerd om dit te vermijden en het nog sneller te maken:
Als status
NOT NULL
is gedefinieerd, kun je vereenvoudigen naar:
AND status <> 'ACTIVE';
Deze query (in tegenstelling tot het momenteel geaccepteerde antwoord van Joel) behandelt NULL waarden niet als gelijk. De volgende twee rijen voor (saleprice, saledate)
zouden kwalificeren als "distinct" (hoewel ze er voor het menselijk oog identiek uitzien):
(123, NULL)
(123, NULL)
Komt ook voor in een unieke index en bijna overal elders, omdat NULL waarden niet gelijk vergelijken volgens de SQL standaard. Zie:
OTOH, GROUP BY
, DISTINCT
of DISTINCT ON ()
behandelen NULL waarden als gelijk. Gebruik een geschikte query stijl afhankelijk van wat u wilt bereiken. U kunt deze snellere query nog steeds gebruiken met IS NOT DISTINCT FROM
in plaats van =
voor elke of alle vergelijkingen om NULL gelijk te maken. Meer:
Als alle kolommen die vergeleken worden NOT NULL
gedefinieerd zijn, is er geen ruimte voor onenigheid.
Het probleem met uw query is dat wanneer u een GROUP BY-clausule gebruikt (wat u in wezen doet door distinct te gebruiken) u alleen kolommen kunt gebruiken waarop u groepeert of aggregatiefuncties gebruikt. Je kunt de kolom id niet gebruiken omdat er potentieel verschillende waarden zijn. In uw geval is er altijd maar één waarde vanwege de HAVING-clausule, maar de meeste RDBMS zijn niet slim genoeg om dat te herkennen.
Dit zou echter moeten werken (en heeft geen join nodig):
UPDATE sales
SET status='ACTIVE'
WHERE id IN (
SELECT MIN(id) FROM sales
GROUP BY saleprice, saledate
HAVING COUNT(id) = 1
)
Je zou ook MAX of AVG kunnen gebruiken in plaats van MIN, het is alleen belangrijk om een functie te gebruiken die de waarde van de kolom teruggeeft als er maar één overeenkomende rij is.