タイトルの通り
調べてみても、 古い記事が多かったので自分用にメモ。

やりたいこと

「tables の column_b(int) に対して column_a(bool) を参照して一括で全部更新したい」

column_b はenumで使いたいためint。
column_a はboolだが、要件がboolで対応できなくなったため column_b をintで作り、
既存処理を column_a から column_b に移したい

一気に column_a を column_b に全部データ書き換えるのがちょっと嫌だったので、
一旦追加して、要件満たした後に column_a を削除するためこの対応をとった。

最初試したこと

column_b に column_a を全部いれるんだから普通にUPDATEすればいけると思い、

1

UPDATE tables SET column_b = (SELECT column_a FROM tables);

と書いたが、 You can’t specify target table ‘tables’ for update in FROM clause

となる。

原因

公式リファレンスにもある通り(弊社はmysql)、どうやら更新や削除を同一テーブルから参照するとデータに不整合が起きる。と理解。

サブクエリーは SELECT ステートメントだけでなく、UPDATE および DELETE ステートメント内でも正当であるため、UPDATE ステートメント内の割り当てのためにサブクエリーを使用できます。ただし、サブクエリーの FROM 句と更新のターゲットの両方に同じテーブル (この場合は、テーブル t1) を使用することはできません。

データが膨大だった場合に、サブクエリで参照しているテーブルのデータが更新中に担保されない危険性がある?のかな。

対応

1
2
3
4
5
6
7
8

select count(*) as "aとbの差分" from tables
where tables.column_a != tables.column_b;
-- 更新件数が何件あるか確認

update tables inner join (select id, column_a from tables) as tmp_tables
on tables.id = tmp_tables.id
set tables.column_b = tmp_tables.column_a
where tables.column_b != tmp_tables.column_a;

一旦自己結合してみた。今回データ量少なかったのもあって実行したけど、大きかったらもっとしっかり調査せねば。。
betterな書き方あったら知りたいです。