Hromadný update v jednom SQL dotazu

V MySQL se dá velice snadno provést několik insertů v jednom dotazu.

INSERT INTO sites (id, url, description) VALUES
(1, "https://k47.cz",        ""),
(2, "https://4chan.org",    ""),
(3, "https://4chan.org/b/", "")

Na druhou stranu update je limitován jenom na jednu změnu. Takže nejjednodušší způsob jak aktualizovat vložené záznamy je provést jeden update pro každý řádek.

UPDATE sites SET description = "k47.cz the best thing on the internets" WHERE id = 1
UPDATE sites SET description = "Internet hate machine"                  WHERE id = 2
UPDATE sites SET description = "Twilight Appreciation Station"          WHERE id = 3

Inza tady popsal (resp. špatně okopíroval), jak hromadného updatu dosáhnout a výsledek je docela pěkný, posuďte sami:

UPDATE sites SET description = CASE
WHEN id = 1 THEN "k47.cz the best thing on the internets"
WHEN id = 2 THEN "Internet hate machine"
WHEN id = 3 THEN "Twilight Appreciation Station"
END
WHERE id IN (1, 2, 3)

Ale existuje ještě jedna technika hroadného updatu, které říkám kolizní insert a využívá MySQL klauzule ON DUPLICATE KEY UPDATE. Jednoduše do tabulky vložíme záznamy s primárním klíčem, který je již obsazen a tedy víme, že nastane kolize a použije se ON DUPLICATE KEY UPDATE, která provede změnu.

Ukázka:

INSERT INTO sites (id, description) VALUES
(1, "k47.cz the best thing on the internets"),
(2, "Internet hate machine"),
(3, "Twilight Appreciation Station")
ON DUPLICATE KEY UPDATE
description = values(description)

Funkce values(description) odkazuje na nově vkládanou hodnotu v první části insertu.

Veliká výhoda je, že se jedná o obyčejný insert, který má něco málo na konci. Takže vygenerovat kolizní insert například s pomocí dibi je triviální:

dibi::query("INSERT INTO sites", $sites, "ON DUPLICATE KEY UPDATE description = values(description)")