Zdarza się, że chcemy w tabeli usunąć duplikaty. Przemilczmy powód, dla którego nie powinny się one tam znaleźć. Natomiast innym powodem, o którym możemy mówić, może być sytuacja gdy po prostu z jakiegoś źródła dostajemy takie dane (przychodzą z urządzenia rejestrującego lub importujemy dane zewnętrzne).
Dajmy za przykład tabelę gdzie przechowujemy temperatury z danego dnia. Ale urządzenie odczytujące temperaturę i zapisujące do bazy danych robi to w ciągu doby kilkukrotnie.
| id | dzien | temperatura |
|---|---|---|
| 1 | 2017-10-15 | 10 |
| 2 | 2017-10-16 | 11 |
| 3 | 2017-10-16 | 12.5 |
| 4 | 2017-10-16 | 11.7 |
| 5 | 2017-10-17 | 13 |
| 6 | 2017-10-17 | 13.2 |
| 7 | 2017-10-17 | 12.8 |
Chcemy pozostawić tylko pierwszą zarejestrowaną temperaturę w danym dniu.
Jak wyprosić niechciane rekordy w tym przypadku?
Musimy znaleźć sposób, aby pozostawić rekordy o id: 1, 2, 5. Na nic tu się zda grupowanie i funkcja MIN. Z pomocą przyjdzie nam CROSS JOIN (iloczyn kartezjański). Czyli musimy każdy rekord tabeli połączyć z każdym, ale jakoś go (iloczyn) ograniczyć aby wybrać wspomniane rekordy. Jeśli czytałeś ten wpis, to może kojarzysz tą metodę, a jeśli nie, to zapytanie będzie wyglądać tak:
SELECT * FROM tabela AS t1, tabela AS t2
WHERE t1.id>t2.id AND t1.dzien=t2.dzien
Póki co jest to zapytanie wybierające, a nie usuwające, ale łatwiej będzie Ci w ten sposób zrozumieć zasadę działania docelowego zapytania. Sprzegając tabelę ze sobą poprzez id (właśnie! to pole musi istnieć i posiadać wzrastającą numerację) z warunkiem nierówności ostrej spowodujemy, że wybrana zostanie część rekordów (pamiętaj, że łączenie rekordów ograniczymy w ramach dnia - drugi warunek w klauzuli WHERE):
| t1.id | t2.id | t1.id>t2.id |
|---|---|---|
| 1 | 1 | 1>1=0 |
| 2 | 2 | 2>2=0 |
| 2 | 3 | 2>3=0 |
| 2 | 4 | 2>4=0 |
| 3 | 2 | 3>2=1 |
| 3 | 3 | 3>3=0 |
| 3 | 4 | 3>4=0 |
| 4 | 2 | 4>2=1 |
| 4 | 3 | 4>3=1 |
| 4 | 4 | 4>4=0 |
| 5 | 5 | 5>5=0 |
| 5 | 6 | 5>6=0 |
| 5 | 7 | 5>7=0 |
| 6 | 5 | 6>5=1 |
| 6 | 6 | 6>6=0 |
| 6 | 7 | 6>7=0 |
| 7 | 5 | 7>5=1 |
| 7 | 6 | 7>6=1 |
| 7 | 7 | 7>7=0 |
Choć jedno id dla którego spełniony jest warunek zostanie wybrane. A które nie zostaną? Oczywiście 1, 2 oraz 5!
Wystarczy teraz przekształcić nasz SELECT w DELETE i pozbędziemy się wszystkich rekordów w danym dniu oprócz najstarszego.
DELETE FROM tabela AS t1, tabela AS t2
WHERE t1.id>t2.id AND t1.dzien=t2.dzien
A jak pozostawić najnowszy rekord w danym dniu? Odwrócić warunek.
DELETE FROM tabela AS t1, tabela AS t2
WHERE t1.id<t2.id AND t1.dzien=t2.dzien
Pamiętaj aby zrobić backup danych przed usuwaniem złego bliźniaka, aby przez przypadek nie usunąć jego dobrych braci.
Przydatne linki:
Przydzielanie rekordów do przedziałów.