k47.cz  — každý den dokud se vám to nezačne líbit
výběr foto Praha povídky kultura
TECH ▞▞ kolo | twitter RSS

Hromadný update v jednom SQL dotazu

— k47 (CC by-sa) (♪)
CC by-nc-sa (via)

V MySQL se dá velice snadno pro­vést ně­ko­lik in­sertů 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 li­mi­to­ván jenom na jednu změnu. Takže nej­jed­no­dušší způsob jak ak­tu­a­li­zo­vat vlo­žené zá­znamy je pro­vé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ě oko­pí­ro­val), jak hro­mad­ného updatu do­sáh­nout a vý­sle­dek je docela pěkný, po­suď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 exis­tuje ještě jedna tech­nika hroad­ného updatu, které říkám ko­lizní insert a vy­u­žívá MySQL klau­zule ON DUPLICATE KEY UPDATE. Jed­no­duše do ta­bulky vlo­žíme zá­znamy s pri­már­ním klíčem, který je již ob­sa­zen a tedy víme, že na­stane kolize a po­u­žije se ON DUPLICATE KEY UPDATE, která pro­vede 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) od­ka­zuje na nově vklá­da­nou hod­notu v první části in­sertu.

Veliká výhoda je, že se jedná o oby­čejný insert, který má něco málo na konci. Takže vy­ge­ne­ro­vat ko­lizní insert na­pří­klad s pomocí dibi je tri­vi­ální:

dibi::query("INSERT INTO sites", $sites, "ON DUPLICATE KEY UPDATE description = values(description)")
píše k47 & hosté, ascii@k47.cz