UPDATE z SELECT w MySQL oraz PostgreSQL
Często dostaje pytanie, jak zrobić UPDATE na całej tabeli z wykorzystaniem polecenia SELECT.
To pytanie powstaje przez błędne założenie, że trzeba znać konkretne ID danego wiersza w bazie danych.
Wiele osób próbuje zatem zrobić pętlę w np. w PHP, która robi ręcznie update na każdym wierszu z osobna.
Wygląda to mniej więcej tak:
Niepoprawne rozwiązanie
<? $subjectArray = $db->sql('SELECT id FROM subject'); foreach ($subjectArray as $key => $value) { $iloscPostowArray = $db->sql('SELECT count(id) AS posts_count FROM posts WHERE subject_id='.$value['id']); $iloscPostow = $iloscPostowArray[0]['posts_count']; $db->sql("UPDATE subject SET posts_count=$iloscPostow WHERE id=".$value['id']); } ?>
Przykładowy schemat naszej bazy
Mamy dwie tabele: subject i posts
Tabela "subject":
- id - id danego tematu
- posts_count - liczba postów w danym temacie
Tabela "posts":
- id - id danego postu
- subject_id - id tematu do którego należy post
Dlaczego powyższe rozwiązanie jest niepoprawne
Z prostego powodu: przy dużej bazie skrypt może nie wyrobić z pamięcią lub nie zmieścić się w czasie wykonywania (nie zawsze mamy możliwość zmodyfikowania odpowiednio plików konfiguracyjnych). Już nie wspomnę nawet o tym, że takie rozwiązanie powoduje u nas ogromną liczbę zapytań SQL.
Poprawne rozwiązanie
UPDATE subject SET posts_count=(SELECT COUNT(id) FROM posts WHERE subject_id = subject.id)
Tak jak widzimy cały kod został sprowadzony do jednego polecenia SQL. Jest to o wiele lepsze rozwiązanie tego problemu.
UWAGA
Przy tworzeniu kolumny działającej na zasadzie naszego posts_count z tabeli subject, pamiętajmy o aktualizowaniu liczby postów najlepiej przy użyciu triggerów
Zapraszam do dyskusji, spisywania innych rozwiązań z użyciem UPDATE i SELECT. Jeżeli znajdziecie jakieś błędy w tym co napisałem, to też proszę mi to zgłosić ;)
Napisane 09 września, 2010 przez popgniezno
