mysql で 最大値を残して delete する

きっかけ

Oracleでは delete from table_a where id < ( select max(id) from table_a ) とやるのだけど、MySQLでは

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

となってしまう。ふと、Oracleの SQL*Plusでは バインド変数を使って手が込んだ削除を行ったのを思い出し、MySQLのユーザ定義変数で同等のことを行うことが思いついた。

連番を振るのに使われるテクニックを応用してみる。

macotasu.hatenablog.jp

ユーザ定義変数への代入を試す

mysq> select ( @max_v := max(id)) from table_a;
+----------------------+
| ( @max_v := max(id)) |
+----------------------+
|            334964562 |
+----------------------+
1 row in set (0.05 sec)

となった。ユーザ定義変数max_vに select の結果が入っているはず。

mysql> select @max_v ;
+-----------+
| @max_v    |
+-----------+
| 334964562 |
+-----------+
1 row in set (0.00 sec)

delete 文にしてみる

mysql> delete from table_a where id < @max_v;