Question Erreur MySQL 1093 - Impossible de spécifier la table cible pour la mise à jour dans la clause FROM


J'ai une table story_category dans ma base de données avec des entrées corrompues. La requête suivante renvoie les entrées corrompues:

SELECT * 
FROM  story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category INNER JOIN 
       story_category ON category_id=category.id);

J'ai essayé de les supprimer en cours d'exécution:

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category 
      INNER JOIN story_category ON category_id=category.id);

Mais je reçois la prochaine erreur:

# 1093 - Vous ne pouvez pas spécifier la table cible 'story_category' pour la mise à jour dans la clause FROM

Comment puis-je surmonter cela?


467
2017-09-05 10:03


origine


Réponses:


Mise à jour: cette réponse couvre la classification d'erreur générale. Pour une réponse plus spécifique sur la meilleure façon de gérer la requête exacte de l'OP, veuillez voir les autres réponses à cette question

En MySQL, vous ne pouvez pas modifier la même table que vous utilisez dans la partie SELECT.
Ce comportement est documenté à l'adresse suivante: http://dev.mysql.com/doc/refman/5.6/fr/update.html

Peut-être que vous pouvez simplement joindre la table à elle-même

Si la logique est suffisamment simple pour redéfinir la requête, perdez la sous-requête et joignez la table à elle-même, en utilisant les critères de sélection appropriés. Cela fera en sorte que MySQL verra la table comme deux choses différentes, permettant ainsi des changements destructeurs.

UPDATE tbl AS a
INNER JOIN tbl AS b ON ....
SET a.col = b.col

Vous pouvez également essayer d'imbriquer la sous-requête plus profondément dans une clause from ...

Si vous avez absolument besoin de la sous-requête, il existe une solution de contournement, mais c'est laid pour plusieurs raisons, y compris la performance:

UPDATE tbl SET col = (
  SELECT ... FROM (SELECT.... FROM) AS x);

La sous-requête imbriquée dans la clause FROM crée un temporaire implicite table, donc cela ne compte pas comme la même table que vous mettez à jour.

... mais attention à l'optimiseur de requêtes

Cependant, méfiez-vous de MySQL 5.7.6 et en avant, l'optimiseur peut optimiser la sous-requête, et toujours vous donner l'erreur. Heureusement, le optimizer_switch variable peut être utilisé pour désactiver ce comportement; Bien que je ne puisse pas recommander de le faire comme quelque chose de plus qu'une solution à court terme, ou pour de petites tâches ponctuelles.

SET optimizer_switch = 'derived_merge=off';

Grâce à Peter V. Mørch pour ce conseil dans les commentaires.

Exemple technique était de Baron Schwartz, publié à l'origine à Nabble, paraphrasé et étendu ici.


598
2017-09-05 10:07



NexusRex à condition très bonne solution pour supprimer avec rejoindre depuis la même table.

Si tu fais ça:

DELETE FROM story_category
WHERE category_id NOT IN (
        SELECT DISTINCT category.id AS cid FROM category 
        INNER JOIN story_category ON category_id=category.id
)

vous allez obtenir une erreur.

Mais si vous enveloppez la condition dans une autre sélection

DELETE FROM story_category
WHERE category_id NOT IN (
    SELECT cid FROM (
        SELECT DISTINCT category.id AS cid FROM category 
        INNER JOIN story_category ON category_id=category.id
    ) AS c
)

ça ferait la bonne chose !!

Explication: L'optimiseur de requête fait un optimisation de fusion dérivée pour la première requête (ce qui provoque l'échec avec l'erreur), mais la deuxième requête ne se qualifie pas pour le optimisation de fusion dérivée par conséquent, l'optimiseur est forcé d'exécuter la sous-requête en premier.


211
2018-03-23 17:23



le inner join dans votre sous-requête est inutile. On dirait que vous voulez supprimer les entrées dans story_category où le category_id n'est pas dans le category table.

Faites ceci:

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category);

Au lieu de:

DELETE FROM story_category 
WHERE category_id NOT IN (
    SELECT DISTINCT category.id 
    FROM category INNER JOIN
         story_category ON category_id=category.id);

99
2018-06-03 21:40



Récemment, j'ai dû mettre à jour des enregistrements dans la même table que je l'ai fait comme ci-dessous:

UPDATE skills AS s, (SELECT id  FROM skills WHERE type = 'Programming') AS p
SET s.type = 'Development' 
WHERE s.id = p.id;

82
2017-10-04 23:39



DELETE FROM story_category
WHERE category_id NOT IN (
    SELECT cid FROM (
        SELECT DISTINCT category.id AS cid FROM category INNER JOIN story_category ON category_id=category.id
    ) AS c
)

29
2017-12-23 20:03



Si vous ne pouvez pas faire

UPDATE table SET a=value WHERE x IN
    (SELECT x FROM table WHERE condition);

parce que c'est la même table, vous pouvez tromper et faire:

UPDATE table SET a=value WHERE x IN
    (SELECT * FROM (SELECT x FROM table WHERE condition) as t)

[mettre à jour ou supprimer ou autre]


16
2018-04-25 11:59



C'est ce que j'ai fait pour mettre à jour une valeur de colonne Priorité de 1 si elle est> = 1 dans une table et de sa clause WHERE en utilisant une sous-requête sur la même table pour vérifier qu'au moins une ligne contient Priorité = 1 condition à vérifier lors de la mise à jour):


UPDATE My_Table
SET Priority=Priority + 1
WHERE Priority >= 1
AND (SELECT TRUE FROM (SELECT * FROM My_Table WHERE Priority=1 LIMIT 1) as t);

Je sais que c'est un peu moche mais ça marche bien.


11
2017-09-02 07:31



Vous pouvez insérer les identifiants de lignes souhaités dans une table temporaire, puis supprimer toutes les lignes de cette table.

ce qui pourrait être ce que @Cheekysoft voulait dire en le faisant en deux étapes.


4
2017-09-05 10:25



Selon  la syntaxe Mysql UPDATE lié par @CheekySoft, il dit tout en bas.

Actuellement, vous ne pouvez pas mettre à jour une table et sélectionner dans la même table dans une sous-requête.

Je suppose que vous supprimez de store_category tout en le sélectionnant dans l'union.


3
2018-01-08 13:18