UPDATE掛ける際にサブクエリを利用する

ちょっとはまったので覚え書きで・・・

問題

MySQLでサブクエリを使ったUPDATEです。

以下のようなテーブルがあったとします。

-- テーブル名：user
id BIGINT(20) auto_increment,
name varchar(20),
school varchar (200)

今回はalter table add columnを利用し、カラムを追加します。

alter table user colmun add `elem_school` varchar(255) not null default "--" after school ;

これでカラムが追加され、追加後のテーブルは以下のようになります。

id BIGINT(20) auto_increment,
name varchar(20),
school varchar (200),
elem_school varchar (255)

ココまでで、「elem_school」には、初期値の「--」が入っています。

この「elem_school」に、「school」の値を入れ直したいって場合が今回衝突した問題です。

テンポラリの作成

テンポラリテーブルとはメモリ上に作成される一時的なテーブルで、セッションが有効な間のみ利用する事が出来ます。

まず、作成するクエリは以下のようになります。

CREATE TEMPORARY tmp SELECT id FROM user ;

「tmp」は好きな名前をつけることが出来ます。

このクエリだけで、一時的なテーブルを作成し、SELECTを行ったカラムと全てのデータをtmpに一時的に保存しています。

サブクエリの注意点

サブクエリは1回以上のSELECTなどを行うと、「Can't reopen [_table_name_]」のように怒られます。

ですので、スマートでは無いですが、tmpテーブルを2つ作成し、対処します・・・。

実際にやってみると

CREATE TEMPORARY tmp2 SELECT id, school_name FROM user ;
CREATE TEMPORARY tmp2 SELECT id, school_name FROM user ;
UPDATE user as a LEFT JOIN tmp1 ON a.id = tmp1.id set elem_school = (SELECT school_name FROM tmp2 WHERE a.id = tmp2.id) ;

こうなります。

みょーにいらっときます。

結果は、「school」カラムに入っていたデータは「elem_school」に登録されています。

途中がすごくぬるく思いますが、許して下さい・・・。

スマートな書き方とか、効率良い書き方、これじゃぁ駄目だよ！などがあれば、コメントいただければと思います。

まだまだ勉強不足中なので、ご教授いただければと思います・・・。