Mysql Subquery Sql Delete Mysql Error 1093

`MySQLエラー1093`の克服 - 更新のための対象テーブルを指定できません

MySQLを使用していると、エラー1093に遭遇することがあります。「FROM句で更新のための対象テーブルを指定できません。」このエラーは、サブクエリで選択しながら、テーブルを更新または削除しようとしたときに発生することが一般的です。このブログ記事では、問題を分解し、クエリをスムーズに実行できるようにするための効果的なソリューションを提供します。

問題の理解

示されたシナリオでは、ユーザーがstory_categoryという名前のテーブルを持っており、その中には壊れたエントリーが含まれています。彼らは、DELETE文の中でサブクエリを使用してこれらのエントリーを削除しようとしました:

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

このクエリを実行すると、以下のエラーメッセージが表示されました:

#1093 - FROM句で更新のための対象テーブル 'story_category' を指定できません

このエラーは、MySQLが同時に選択しているテーブル（この場合、story_category）を変更することを許可しないために発生します。この制限を克服する方法を探ります。

ソリューション戦略

1. セルフジョインを使用する

このエラーを回避する効果的な方法の一つは、セルフジョインを使用することです。この方法では、対象テーブルへのサブクエリの直接使用を避けるために、クエリの構造を再構築できます。以下がその例です:

DELETE a
FROM story_category AS a
INNER JOIN category AS b 
ON a.category_id = b.id
WHERE b.id IS NULL;

この例では:

story_category AS a は、変更（または削除）したい主なテーブルを示しています。
category AS b は、結合するテーブルです。
存在しないカテゴリーIDのみが一致するようにすることで、story_categoryから壊れたエントリーを削除します。

2. サブクエリをネストする

サブクエリを使用する必要がある場合は、メインクエリ内でさらに深くネストして、暗黙的な一時テーブルを作成することを考慮してください:

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

このアプローチは、MySQLが内部のサブクエリを一時テーブルとして扱うことにより、ターゲットテーブルの直接的な変更を避けることで、エラーを回避します。

3. クエリオプティマイザの調整

MySQLバージョン5.7.6から、クエリオプティマイザに変更が加えられましたが、ネストされたサブクエリのアプローチを使用してもこのエラーが発生することがあります。この問題に遭遇した場合、一時的にオプティマイザのスイッチを調整することができます:

SET optimizer_switch = 'derived_merge=off';

このコマンドは、MySQLに派生テーブルをマージしないよう指示します。これにより、クエリの実行が助けられるかもしれません。ただし、これは短期的なソリューションまたは一時的なタスクのために扱うべきであり、クエリのパフォーマンスや結果に影響を与える可能性があるため、注意が必要です。

結論

テーブル内のエントリーを削除または更新しようとする際にMySQLエラー1093に遭遇することは一般的な障害ですが、この問題を乗り越えるための効果的な戦略があります。セルフジョインを使用するか、サブクエリをネストするか、オプティマイザの設定を調整するか、データベースのパフォーマンス目標に合った方法を選択することが重要です。

これらのソリューションを探求し、ご自身の特定のデータベース構造やニーズに応じて適応してください。クエリを楽しんでください！