MySQL グループ化した条件で取得したデータを削除 / 変更にハマる

テーブル table_a

id	name	typeID
1	星宮いちご	1
2	霧矢あおい	2
3	紫吹蘭	3
4	大空あかり	1

table_aのtypeIDカラムのデータが重複してるレコードを削除しようとして次のようなSQLを発行しました。

DELETE FROM table_a
WHERE typeID IN (
  SELECT typeID FROM table_a
  GROUP BY typeID
  HAVING COUNT(*) >= 2
);

You can't specify target table 'table_a' for update in FROM clauseというエラーになってしまいました。

どうやら、DELETE, UPDATEでデータを操作するテーブルと同じ名前がサブクエリ内にあるとダメなようです。

このエラーは、テーブルを変更し、さらにサブクエリーで同じテーブルから選択しようとする次のような場合に発生します。
UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
サブクエリーは SELECT ステートメントだけでなく、UPDATE および DELETE ステートメント内でも正当であるため、UPDATE ステートメント内の割り当てのためにサブクエリーを使用できます。ただし、サブクエリーの FROM 句と更新のターゲットの両方に同じテーブル (この場合は、テーブル t1) を使用することはできません。
出典: [https://dev.mysql.com/doc/refman/5.6/ja/subquery-errors.html:title]

サブクエリで取得してる部分を更にサブクエリにしてASで別名にして取得するようにすればOK

ニホンゴで書くと判りにくいけど、要はこんな感じ

DELETE FROM table_a
WHERE typeID IN (
  SELECT typeID FROM (
    SELECT typeID FROM table_a
    GROUP BY typeID
    HAVING COUNT(*) >= 2
  ) AS tmp
);

サブクエリで取得しているtypeIDカラムのデータが重複している部分が、DELETE する際のクエリではtmp扱いになって、同じテーブルの名が重複しなくなるということっぽい。

ただ上記のクエリでは、実際に削除するために選択しているレコードはこんな感じになります。

mysql >
SELECT * FROM table_a
WHERE typeID IN (
  SELECT typeID FROM table_a
  GROUP BY typeID
  HAVING COUNT(typeID) >= 2
);

+----+-----------------+--------+
| id | name            | typeID |
+----+-----------------+--------+
|  1 | 星宮いちご        |      1 |
|  4 | 大空あかり        |      1 |
+----+-----------------+--------+

なので実際に削除を実行すると…

mysql >
DELETE FROM table_a
WHERE typeID IN (
  SELECT typeID FROM (
    SELECT typeID FROM table_a
    GROUP BY typeID
    HAVING COUNT(*) >= 2
  ) AS tmp
);

Query OK, 2 rows affected (0.10 sec)

mysql > SELECT * FROM table_a;

+----+-----------------+--------+
| id | name            | typeID |
+----+-----------------+--------+
|  2 | 霧矢あおい        |      2 |
|  3 | 紫吹蘭           |      3 |
+----+-----------------+--------+