phpundmysql.de

Wer die Feinheiten der Syntax kennt, kann sich ihne viel Arbeit oft selbst aus der Patsche helfen. Das INSERT ... ON DUPLICATE KEY UPDATE, kurz UPSERT, ist so ein Fall. Ohne UPSERT hat man in der Regel zwei Wege herauszufinden, ob man beim Befüllen der Datenbank ein INSERT oder ein UPDATE benötigt:

1. Ein SELECT absetzen, das genau nach der Kombination von Schlüsselattributen sucht, die man einfügen möchte. Liefert das SELECT einen Datensatz zurück, dann macht man ein UPDATE, sonst ein INSERT. Oder
2. Gleich ein INSERT absetzen und prüfen, ob ein ERROR 1062 (23000): Duplicate entry... zurückkommt. Wenn ja, dann ein UPDATE hinterherschieben.

Beides erzeugt wesentlich zu viel Aufwand. Nehmen wir beispielsweise folgende MySQL-Tabelle

CREATE TABLE upsert (
id INT(11) NOT NULL,
updated_on TIMESTAMP NOT NULL,
value VARCHAR(100) NOT NULL,
PRIMARY KEY (id));

Die Tabelle ist sehr einfach strukturiert: id dient als Primärschlüssel (ohne AUTO INCREMENT), updated_on hält den Zeitpunkt der letzten Änderung fest und value speichert irgendeinen Wert. Mit

INSERT INTO upsert (id, updated_on, value) VALUES (1, CURRENT_TIMESTAMP, 'abc');

wird ein erster Datensatz eingefügt. Wird dasselbe Statement noch einmal abgesetzt, kommt es zum oben beschriebenen Fehler 1062. Ist der Tabelleninhalt beim Einfügen nicht bekannt, hilft uns das UPSERT weiter:

INSERT INTO upsert (id, updated_on, value) VALUES (1, CURRENT_TIMESTAMP, 'abc')
ON DUPLICATE KEY UPDATE updated_on = CURRENT_TIMESTAMP, value = 'abc';

Die Schlüsselattribute müssen im zweiten Teil des Befehls natürlich nicht mit angegeben werden, denn bei dem alternativen UPDATE verändern sie sich naturgemäß nicht. Anstatt im UPDATE dieselben Parameter wie im INSERT zu nutzen, kann man auch andere Werte zuweisen.
Noch einfacher ist natürlich, wenn man die Parameter (CURRENT_TIMESTAMP und 'abc') nicht zweimal in seinen Befehl einbauen muss. Das hilft besonders bei der Verwendung von Prepared Statements. Diesen Komfort erreicht man durch die Verwendung des VALUES() innerhalb des UPDATE-Teils. Damit hat man Zugriff auf die Werte, die man im vorderen Teil des Befehls festgelegt hat.

INSERT INTO upsert (id, updated_on, value) VALUES (1, CURRENT_TIMESTAMP, 'abc')
ON DUPLICATE KEY UPDATE updated_on = VALUES(updated_on), value = VALUES(value);

2 ROWS AFFECTED???

Bei genauerer Betrachtung fällt noch eine Eigenheit des UPSERT auf. Auch wenn sich nur ein Datensatz in der Tabelle befindet, vermeldet MySQL die erfolgreiche Ausführung des UPSERT mit

Query OK, 2 rows affected (0.00 sec)

Das ist kein Fehler, sondern ein gewünschtes und dokumentiertes Verhalten. Wird bei einem UPSERT eine Zeile eingefügt, meldet MySQL 1 row affected, muss stattdessen eine Aktualisierung vorgenommen werden, sind es eben 2.