数日前、またしても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って、生理的に受け付けられません。もっと上手く取り扱いたいものですね。
検証環境
- MySQL : 5.1.61 ※MyISAMでのみ検証
- PostgreSQL : 9.1.4
- OS : CentOS