MySQL : Faire un UPDATE multiple en une seule fois

Vous vous êtes sûrement déjà posé la question : comment faire un UPDATE sur ma table (MySQL) qui contient plusieurs milliers d'enregistrements en une seule fois ?

L’intérêt, bien sûr c'est d'éviter de faire des milliers d'UPDATE. Le gain en ressources et en temps est conséquent !

Prenons l'exemple de votre boutique en ligne reliée à votre ERP, et vous souhaitez mettre à jour les prix sur votre site.

La table product_price de votre site serait de ce type:

product_price
product_id price
5231 22.30
4215 10.23
617 51.50
5510 1.22

Si vous avez une grosse boutique en ligne, vous aurez donc des milliers d'enregistrements dans cette table.

Imaginons maintenant que vous fassiez une requête (SQL ou WebServices) sur votre ERP qui vous retournerait un tableau PHP contenant l'id du produit + le prix.

Pour les besoins de cet article, je crée le tableau à la main comme ceci :

Le tableau PHP contiend donc les valeurs ci-dessous :

Pour mettre à jour les prix de la boutique Web, nous allons utiliser la requête INSERT + ON DUPLICATE KEY UPDATE

L'instruction ON DUPLICATE KEY n'est valable que dans le cas d'utilisation d'une clef primaire (ici : id).

Attention, cette fonction n'est pas disponible sur PostgreSQL

Vous voyez la syntaxe après VALUES : chaque groupe de valeurs est placé entre parenthèses et séparés par une virgule.

On peut ainsi mettre plusieurs milliers de groupes de valeurs sans aucun problème.
Lors de l'exécution de la requête, chaque groupe de valeurs va être traité l'un après l'autre. Vous vous en doutez, comme chaque id existe déjà et que c'est une clef primaire, la requête INSERT sera ignorée et la requête UPDATE sera exécutée.

Note: vous pouvez faire des groupes de valeurs avec plusieurs champs, le principal étant que la clef primaire soit dans le lot. Dans ce cas, vous devrez également rajouter les champs à mettre à jour ( column1=VALUES(column1), column2=VALUES(column2), etc.)

Dans le cas qui nous préoccupe, nous allons préparer les données du tableau PHP pour ensuite préparer la requête.
Nous allons parcourir le tableau et formater les données pour les remettre ensuite dans un autre tableau comme ci dessous :

Ce qui nous donnera le tableau suivant :

Puis nous écrivons ensuite la requête :

Ce qui donnera :

il ne restera plus qu'à exécuter la requête (PDO):

Notes:
La connexion à la DB ($connection) a été initiée en amont.

Après la requête, les prix auront été mis à jour comme ci-dessous

product_price_updated
product_id price
5231 22.50
4215 8.20
617 52.05
5510 1.55