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って、生理的に受け付けられません。もっと上手く取り扱いたいものですね。