自分が苦手なので、備忘録的に残しておきます。

まずやりたいことはこれです。RDB （今回は MySQL）のこんなテーブル(todos)があったとします。email の人の ToDo を管理するようなテーブルだと考えてください：

列名	列型	目的
id	int	プライマリキー、自動インクリメント
email	varchar(50)	メールアドレス
body	varchar(255)	本文
created	long	レコードを作成した日時のタイムスタンプ

これだけであれば、以下の SQL でテーブルを定義するようなものだと思います：

create table todos(
  id int primary key auto_increment,
  email varchar(50),
  body varchar(255),
  created long
);

ここに運用上の条件を１つ加えます。１つの email に対しては最新の body のみを有効としたいのです。要するに同じ email に異なる body があっても（なくても）いいのですが、有効なものは最新の（created が最も大きな）body のみとして扱えるようにしたい、というものです。

例えばテーブルの中身がこのようになっているものとします（タイムスタンプの値がいいかげんですが、大小関係だけ分かればいいものとします）：

id	email	body	created
1	aaa@xxx.jp	ToDo その１	10
2	bbb@xxx.jp	ToDo その２	20
3	aaa@xxx.jp	ToDo その３	30

全部で３件のレコードがありますが、email = aaa@xxx.jp のレコードが２件あります。この場合は created が最大である id=3 のレコードのみが有効と考えます（id=1 のレコードは削除されていても構いません）。

このような条件を付けた上で、「有効なレコードだけの一覧を取り出したい」のですが、どのようにデータベースを定義して運用すればいいでしょう？

まず email 列は実質的にユニークになるとも考えられます。が、テーブル定義時に UNIQUE 属性を付けてしまうと、INSERT 時にエラーとなってしまうので「まず同じ email のデータが存在しているかどうかを確認し、存在していなければ INSERT 、していれば UPDATE するような SQL を実行する」ことになります（こうすると実は UNIQUE 属性はあってもなくても同じと言えます）。INSERT 時の処理がちと面倒ですが、読み込み時にはシンプルに "select * from todos" だけで実現可能な方法といえます。

次に email はユニークとは考えずに、とにかくレコードは全て INSERT して、有効なレコードを取り出す時に工夫する、という方法もあります。これは SQL としては以下を実行すればよいことになります：

> select * from todos where ( email, created ) in (select email,max(created) as max_created from todos group by email);

これはこれで実現可能な方法の１つです。ただ上記の例とは逆に、INSERT は無条件に行いますが、読み込み時に比較的複雑な SQL が実行されることになります。また有効でない（使われることのない）レコードがテーブル内にずっと残ってしまうことになります。


ではこれらのいいとこ取りを考えてみます。つまり、以下のようなロジックです：
(1) INSERT は無条件に行い、
(2) INSERT 直後に有効でないレコードがあったら削除し、
(3) 検索はシンプルに "select * from todos" だけで行えるようにする


(1) の直後に (2) が出来れば、(1) も (3) もシンプルな処理になるので理想的とも言えます。で、前書きが長くなりましたが、今回はこの (2) を実現するのに苦労した話です（苦笑）。

まず、このケースでの「有効なレコード」を取り出す場合の SQL クエリーは上記のようにこのようになります：

> select * from todos where ( email, created ) in (select email,max(created) as max_created from todos group by email);

（結果）

id
email
body
created
2
bbb@xxx.jp
ToDo その２
20
3
aaa@xxx.jp
ToDo その３
30

ということは、「有効でないレコード」（削除対象のレコード）はこの SQL で取り出せます：

> select * from todos where ( email, created ) not in (select email,max(created) as max_created from todos group by email);

（結果）

id
email
body
created
1
aaa@xxx.jp
ToDo その１
10

ここまでできれば後は単純に削除対象のレコードを削除すればよいので、サブクエリーを使ってこんな SQL を実行するだけ・・・

> delete from todos where id in ( select id from todos where ( email, created ) not in (select email,max(created) as max_created from todos group by email) );

と思ったのですが、これを実行すると以下のようなエラーが発生します：

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

これがサブクエリーのややこしい（というか、自分がよく分かっていなかった）所です。サブクエリーを使った時に意識する構文がいくつか存在しています：
https://dev.mysql.com/doc/refman/5.6/ja/subquery-errors.html

↑これの「サブクエリー内の誤って使用されているテーブル」に書かれた項目が今回のエラーの原因でした。サブクエリーの FROM で指定されるテーブルと、更新系のテーブルに同じものを使用することができない、というルールです。

これを解決するにはサブクエリー内の FROM 部分（"todos" と書かれた部分）を更にサブクエリーにしてテンポラリテーブルにします。上記例だとこんな感じ：

> delete from todos where id in ( select id from (select * from todos) where ( email, created ) not in ( select email,max(created)
 as max_created from (select * from todos) group by email ) );

ただこれだと該当部分が２箇所あり、今度は複数のテンポラリテーブルは異なるエイリアスを持たなければならない、という制約に引っかかってエラーになってしまいます：

ERROR 1248 (42000): Every derived table must have its own alias

というわけで、最終的な答はこちら。テンポラリテーブルに異なるエイリアスを指定して完成です：

> delete from todos where id in ( select id from (select * from todos) as temp1 where ( email, created ) not in ( select email,max(created)
 as max_created from (select * from todos) as temp2 group by email ) );

こんな SQL 、何も見ずに一発でさらっと書ける人いるんかな・・・

タグ ：

#mysql

#sql

#subquery