職業プログラマの休日出勤

職業プログラマによる日曜自宅プログラミングや思考実験の成果たち。リアル休日出勤が発生すると更新が滞りがちになる。記事の内容は個人の意見であり、所属している(いた)組織の意見ではない。

update文中でのsubquery

数日前、またしてもMySQLの罠?に引っ掛かってしまったのでメモ。
どうやら、update文中のsubqueryでは、update対象テーブルをselectしてはいけないらしいです。

テスト用テーブル

create table test20120831(
 id int primary key,
 category_name text,
 data text
)

テストデータ

insert into test20120831 values (1, 'hogehoge', null);
insert into test20120831 values (2, 'hogehoge', null);
insert into test20120831 values (3, 'hogehoge', null);
insert into test20120831 values (4, 'foobar', null);
insert into test20120831 values (5, 'foobar', null);

問題のSQL

update test20120831
set data = 'I am updated!'
where id in (select min(id) from test20120831 group by category_name);

これをPostgreSQLで実行すると、次のような結果が得られます。

postgres=# select * from test20120831 order by id;
 id | category_name |     data      
----+---------------+---------------
  1 | hogehoge      | I am updated!
  2 | hogehoge      | 
  3 | hogehoge      | 
  4 | foobar        | I am updated!
  5 | foobar        | 
(5 rows)

たぶんOracleも同じように動作するはずです。

ところがMySQLでは

#1093 - You can't specify target table 'test20120831' for update in FROM clause

と怒られてしまいます。
マニュアルにもできないって明記されてますね。
MySQL :: MySQL 5.1 Reference Manual :: 13.2.9.9 Subquery Errors

こういう制限がかかってくるのは、ロック獲得まわりに起因する問題なんでしょうね。

Work Around

この問題を回避するには、このStackOverflowのQAが役に立ちそうです。
http://stackoverflow.com/questions/4268416/sql-update-with-sub-query-that-references-the-same-table-in-mysql

但し、このQAでは group by 句を取り扱っていません。今回の問題の為に応用させると

update test20120831 a
inner join (select min(id) as max_id from test20120831 group by category_name) b 
on b.max_id = a.id
set data = 'I am updated!';

といったところでしょうか。これは上手く動作しました。
動作してくれるのは嬉しいけど、update文でjoinって、生理的に受け付けられません。もっと上手く取り扱いたいものですね。

検証環境