UPDATE et SELECT sur la même table

17 décembre 2010 par stephane

Utiliser MySQL suppose de vivre avec certaines de ses limitations. Voici comment se débrouiller avec l’une d’entre elles : comment exécuter un UPDATE ou un DELETE sur une table sur laquelle on exécute en même temps un SELECT ?

Ce genre de situation peut se produire lorsqu’on souhaite mettre à jour un champ d’une table avec des données agrégées provenant de la même table. Pas clair ? Alors voici un exemple simple :

CREATE TABLE items (
  item_id int NOT NULL AUTO_INCREMENT,
  purchase_date date DEFAULT NULL,
  total int NOT NULL,
  total_month int NOT NULL,
  PRIMARY KEY (item_id)
);

INSERT INTO items (purchase_date,total) VALUES ('2010-12-01',10),
('2010-12-05',5),
('2010-10-10',14),
('2010-11-20',9),
('2010-10-09',3);

Cela nous donne les données suivantes :

mysql> SELECT * FROM items;
+---------+---------------+-------+-------------+
| item_id | purchase_date | total | total_month |
+---------+---------------+-------+-------------+
|       1 | 2010-12-01    |    10 |           0 |
|       2 | 2010-12-05    |     5 |           0 |
|       3 | 2010-10-10    |    14 |           0 |
|       4 | 2010-11-20    |     9 |           0 |
|       5 | 2010-10-09    |     3 |           0 |
+---------+---------------+-------+-------------+

Supposons que je souhaite remplir la colonne total_month avec le total des ventes du mois. La requête correspondante sera :

UPDATE items t1 SET total_month=(
  SELECT SUM(total) FROM items t2 WHERE   MONTH(t2.purchase_date)=MONTH(t1.purchase_date)
);

Problème, quand j’essaie d’exécuter la requête, MySQL m’envoie balader avec l’erreur suivante :

ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause

Effectivement, quand on regarde dans la doc en ligne, tout en bas de la page, on voit qu’on ne peut pas mettre à jour une table en effectuant une sous-requête sur la même table pour obtenir les données nécessaires.

Fin de l’histoire ? Non, bien sûr, il est toujours possible d’écrire un peu de PHP ou de Perl pour boucler sur tous les éléments de la table et exécuter séparément le SELECT et l’UPDATE.

On peut aussi essayer de rester dans le domaine des requêtes SQL. Évidemment, comme il s’agit de réécrire la requête, il va falloir repenser la requête en français d’abord, puis la traduire en SQL. La requête qui ne fonctionnait pas était pensée en termes de boucle (pour chaque ligne de la table, rechercher le total du mois correspondant), ce qui s’est traduit par une sous-requête. Comme on sait qu’une sous-requête peut souvent être réécrite avec une jointure et qu’une jointure représente une opération ensembliste, repensons notre requête en termes ensemblistes (voir cet autre article sur le sujet).

Nous avons déjà un ensemble, qui est la table items elle-même. Il est facile de constituer un second ensemble qui est l’ensemble des totaux par mois :

SELECT MONTH(purchase_date) purchase_month,SUM(total) t_month FROM items GROUP BY purchase_month;

Il ne reste plus qu’à joindre nos deux ensembles en sachant que l’intersection se fait sur le mois d’achat :

UPDATE items INNER JOIN
  (SELECT MONTH(purchase_date) purchase_month,SUM(total)   t_month FROM items GROUP BY MONTH(purchase_date)) t
ON MONTH(items.purchase_date)=purchase_month
SET total_month=t_month;

Et maintenant, MySQL accepte bien d’exécuter la requête !

mysql> SELECT * FROM items;
+---------+---------------+-------+-------------+
| item_id | purchase_date | total | total_month |
+---------+---------------+-------+-------------+
|       1 | 2010-12-01    |    10 |          15 |
|       2 | 2010-12-05    |     5 |          15 |
|       3 | 2010-10-10    |    14 |          17 |
|       4 | 2010-11-20    |     9 |           9 |
|       5 | 2010-10-09    |     3 |          17 |
+---------+---------------+-------+-------------+

Il reste une question à se poser : la deuxième requête étant toujours un UPDATE avec un SELECT sur la même table, pourquoi cette deuxième variante fonctionne-t-elle ?

La commande EXPLAIN va nous donner la réponse. Comme il n’est possible de lancer EXPLAIN que sur des requêtes de type SELECT, il nous faut transformer notre UPDATE en un SELECT à peu près équivalent :

mysql> EXPLAIN SELECT * FROM items INNER JOIN (SELECT MONTH(purchase_date) purchase_month,SUM(total) t_month FROM items GROUP BY MONTH(purchase_date)) t ON MONTH(items.purchase_date)=purchase_month\G
***************** 1. row *****************
id: 1
select_type: PRIMARY
table:
type: ALL
...
rows: 3
Extra:
***************** 2. row *****************
id: 1
select_type: PRIMARY
table: items
...
rows: 5
Extra: Using where; Using join buffer
***************** 3. row *****************
id: 2
select_type: DERIVED
table: items
...
rows: 5
Extra: Using temporary; Using filesort

En bref, on voit que MySQL matérialise le SELECT par une table temporaire, qui est ensuite jointe à la table items. Voilà le secret de la réussite de notre seconde requête : une « derived table » est toujours représentée par une table temporaire, ce qui nous autorise à contourner la limitation de MySQL sur les requêtes avec UPDATE et SELECT.

L’analyse étant terminée, on peut entrevoir une autre possibilité pour faire fonctionner la première requête. En effet, le succès de la seconde requête repose sur la création d’une table temporaire. On peut donc s’arranger pour forcer la création d’une table temporaire dans le SELECT de la première requête, dès lors qu’on sait qu’on obtiendra toujours une table temporaire pour une sous-requête dans la clause FROM :

UPDATE items t1 SET total_month=(SELECT SUM(total) FROM (select * FROM items) t2 WHERE MONTH(t2.purchase_date)=MONTH(t1.purchase_date));

Comme quoi, EXPLAIN ne sert pas seulement à optimiser les requêtes…

Mots-clefs : ,

2 commentaires sur “UPDATE et SELECT sur la même table”

  1. NEO DB Blaster dit :

    Je dirais même mieux :
    UPDATE items INNER JOIN
    (
    SELECT
    MONTH(purchase_date) purchase_month,
    YEAR(purchase_date) purchase_year,
    SUM(total) t_month FROM items
    GROUP BY MONTH(purchase_date) , YEAR(purchase_date)
    ) t
    ON
    MONTH(items.purchase_date)=purchase_month
    AND
    YEAR(items.purchase_date)=purchase_year
    SET total_month=t_month;

  2. Ben dit :

    Je vous remercie pour la qualité de votre article. Cela m’a épargné une grande perte de temps en évitant la création de tables temporaires manuellement. Il faut dire que les solutions pour contourner cette limitation de mySQL #1093 ne court pas les rues (enfin les pages web), ni la doc officielle.

    Pour ma part, j’ai adapté votre solution dans l’objectif de fusionner 2 colonnes de la même table avec la requête suivante :

    UPDATE test
    INNER JOIN
    (SELECT id par_id, CONCAT(nom, ‘ ‘, type) par_type FROM test) t
    ON test.id = par_id
    SET test.type = par_type;

    ALTER TABLE test DROP nom

Laisser une réponse