PoP_Gniezno

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ć ;)