MySQLでサブクエリを使用した更新に苦戦したこと[MySQL_ERROR#1093] #MySQL

はじめに

現場で何気なく使っていたMySQLですが、サブクエリを使用した更新（update）で躓いたため備忘録ついでに記載します。

説明準備

説明に使用するテーブルを準備します。
(なお、今回の実行確認にはpaiza.IOを使用しています）

example

-- テーブル定義
create table sampleTabele(
    auto_number integer AUTO_INCREMENT NOT NULL PRIMARY KEY, 
    group_id varchar(100) comment 'グループid',
    invalid_flg integer comment '有効/無効フラグ', 
    name varchar(100) comment '名前',
    age integer comment '年齢', 
    address varchar(100) comment '住所'
);

-- テストデータ投入
insert into sampleTabele( group_id, invalid_flg, name, age, address) 
values ("G001",1,"田中太郎",29,"大阪"),
    ("G001",1,"田中太郎",30,"名古屋"),
    ("G002",1,"佐藤健",19,"埼玉"),
    ("G001",0,"田中太郎",31,null),
    ("G002",0,"佐藤健",20,null);

【実行結果】

auto_number	group_id	invalid_flg	name	age	address
1	G001	1	田中太郎	29	大阪
2	G001	1	田中太郎	30	名古屋
3	G002	1	佐藤健	19	埼玉
4	G001	0	田中太郎	31	NULL
5	G002	0	佐藤健	20	NULL

※有効/無効は以下の通り定義します。
invalid_flg = 0 : 有効
invalid_flg = 1 : 無効

今回やりたかったこと

同一グループに属している無効レコードから有効なレコードへデータを引き継ぐ。
無効レコードが複数ある場合は一番新しい無効レコードのデータを使用すること。

何が起きたのか

Select文で更新対象、引き継ぎ対象データを確認してからupdate（delete）文へ作り変えたらERROR#1093が返ってきた。

1.更新対象、引き継ぎデータの確認
まず、更新対象と引き継ぎ用データは以下のSQLで確認しました。

【更新対象と引き継ぎ用データのマッピング】

select 
    target.auto_number,
    target.group_id,
    target.invalid_flg,
    target.address,
    updd.auto_number,
    updd.group_id,
    updd.invalid_flg,
    updd.address 
from sampleTabele target            -- 更新対象
inner join sampleTabele updd        -- 引き継ぎ用データ
    on target.group_id = updd.group_id
where target.invalid_flg = 0
    and updd.auto_number in
        (   -- 最新の無効データ抽出
            select max(auto_number) as auto_number
            from sampleTabele
            where invalid_flg = 1
            group by group_id
        )
;

【実行結果】

auto_number	group_id	invalid_flg	address	auto_number	group_id	invalid_flg	address
4	G001	0	NULL	2	G001	1	名古屋
5	G002	0	NULL	3	G002	1	埼玉

2.更新用SQL（update文）への書き換え
1.で問題なくデータが取得できたので、気にせずそのままupdate文に作り変え！

【更新用SQLへの書き換え】


update sampleTabele target
inner join sampleTabele updd
    on target.group_id = updd.group_id
set
    target.address = updd.address 
where target.invalid_flg = 0
    and updd.auto_number in
        (
            select max(auto_number) as auto_number
            from sampleTabele
            where invalid_flg = 1
            group by group_id
        )
;

【実行結果】
ERROR 1093 (HY000) at line 67: You can't specify target table 'target' for update in FROM clause

Selest文は問題なくデータが取得できたのだし、update文だって問題ないと思っていたのに返ってきたのはERRORコード…

困ったときのGoogle先生にERRORコードをお尋ねしたところ、
「mySQLでは更新対象テーブルと同じテーブルを更新条件のサブクエリに使用することができない」とのご返答をいただきました。
ERROR#1093

解決策

え？対象データ取れてるし、更新対象はキー項目でもないのに…とMySQLの仕様に悪態をつきつつ解決方法を模索してみる。
調べてみるとサブクエリ部分にエイリアスをつけたり、結合条件としての使用ならば動きそう…

なので、以下の通りに修正してみる。

【修正案】

update sampleTabele target           --  更新対象
    inner join sampleTabele updd     --  引き継ぎ用データ
        on target.group_id = updd.group_id
    inner join (     --  最新の無効データ抽出
            select max(auto_number) as auto_number
            from sampleTabele
            where invalid_flg = 1
            group by group_id
        ) tmp
        on updd.auto_number = tmp.auto_number
set
    target.address = updd.address 
where target.invalid_flg = 0
;

【実行結果】

auto_number	group_id	invalid_flg	name	age	address
1	G001	1	田中太郎	29	大阪
2	G001	1	田中太郎	30	名古屋
3	G002	1	佐藤健	19	京都
4	G001	0	田中太郎	31	名古屋
5	G002	0	佐藤健	20	埼玉

じょうずにできました！

ちなみに、今回ERRORになったSQLをMySQL以外の環境で実行した場合は問題なく実行されるそうです。
（MySQLの仕様め…）

まとめ

基本が似ていても、それぞれの仕様を理解していないと今回のような混乱が生まれることを実感しました。
~~でも正直ややこしいから使えるように統一してしてほしい…~~