マッピングテーブルを作ってセキュアに本番データを更新する方法

# マッピングテーブルを作ってセキュアに本番データを更新する方法
人生長年生きてれば、本番データ変更してほしい依頼を受けた経験は数回あるはずです。

安心にデバックしやすく本番データを更新する方法を記載します。

■ 前提条件

```
- ポスグレ 想定
- SQLある程度わかる
```

# 依頼内容例
依頼として以下のような関連性を持つようなデータ関係に更新してほしいという依頼が来たと想定します。

|  item_code  |  picture_code  |
| ---- | ---- |
|  'I001'  | 'P001'  |
|  'I001'  | 'P009'  |
|  'I001'  | 'P0s2'  |
|  'I002'  | 'P0s01'  |
|  'I002'  | 'P001'  |
|  'I003'  | 'P002'  |
|  'I004'  | 'P003'  |
|  'I005'  | 'P004'  |

例） item_codeが'I001'の時picture_codeは'P001'になる

■ モデル構成
![スクリーンショット 2020-10-09 23.49.42.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/286094/6138afdf-2f71-71db-13cb-1f1bd21d7dad.png)


# STEP1: temp_tableを作成する

更新専用の一時的に利用するtempテーブル作成します。

``` sql
create temp table temp_table (
                                item_id integer,
                                item_code character varying,
                                picture_code character varying
                            );
```

# STEP2: tempテーブルに必要な情報をinsertする

イメージとしては**マッピング情報**をinsertするような感じ

``` sql
insert into temp_table values
(null, 'I001', 'P001'),
(null, 'I001', 'P009'),
(null, 'I001', 'P0s2'),
(null, 'I002', 'P0s01'),
(null, 'I002', 'P001'),
(null, 'I003', 'P002'),
(null, 'I004', 'P003'),
(null, 'I005', 'P004');
```

# STEP3: itemコードとitem_idを紐付け
現状tempテーブルのitem_idがnullなので商品テーブルから対象のレコードを探して出してきて（where  temp_table.item_code = items.item_code;）、ヒットしたidをtempテーブルのitem_idにUPDATEしてあげる。（伝われ！）

``` sql
update temp_table
set    item_id = items.id
from   items
where  temp_table.item_code = items.item_code;
```

# STEP4: 紐付けできたか確認


``` sql
select count(*)
from temp_table
where item_id is null;
```

結果が以下のように０の場合temp_tableのitem_idに全ての値が入力されたというSTEP3が無事に成功した証明になる。

``` sql
 count
-------
     0
(1 row)
```

# STEP５: 写真モデルのFKを更新
``` sql
update pictures
set    item_id = temp_table.item_id
from   temp_table
where  pictures.pictures_code = temp_table.pictures_code;
```

これで以下のようなマッピングできた状態を実現できる

|  item_code  |  picture_code  |
| ---- | ---- |
|  'I001'  | 'P001'  |
|  'I001'  | 'P009'  |
|  'I001'  | 'P0s2'  |
|  'I002'  | 'P0s01'  |
|  'I002'  | 'P001'  |
|  'I003'  | 'P002'  |
|  'I004'  | 'P003'  |
|  'I005'  | 'P004'  |

# STEP６: temp_tablesをdrop

もう必要ないのでdropする

``` sql
drop table temp_table;
```

# 一連の流れ

``` sql
-- 本番データ触る時は必ずトランザクション貼る
BEGIN;

-- tempテーブル作成
create temp table temp_table (
                                item_id integer,
                                item_code character varying,
                                picture_code character varying
                            );

-- insertする
insert into temp_table values
(null, 'I001', 'P001'),
(null, 'I001', 'P009'),
(null, 'I001', 'P0s2'),
(null, 'I002', 'P0s01'),
(null, 'I002', 'P001'),
(null, 'I003', 'P002'),
(null, 'I004', 'P003'),
(null, 'I005', 'P004');

-- temp_tableのitem_idをUPDATE
update temp_table
set    item_id = items.id
from   items
where  temp_table.item_code = items.item_code;

-- temp_tableのitem_idが全部UPDATEされているかの確認
select count(*)
from temp_table
where item_id is null;

-- picturesの外部キーを対象の商品idに変更
update pictures
set    item_id = temp_table.item_id
from   temp_table
where  pictures.pictures_code = temp_table.pictures_code;

-- 完了したら、temp_tableは必要ないので削除
drop table temp_table;

-- 問題なければCOMMITしちゃいましょう
COMMIT;
```

# まとめ
temp_tableを作ることで、デバックしやすくなる。

失敗しても、temp_tableをDropすればデータの欠陥も最小限にすむなどのメリットもあります。

まあ、別にわざわざtemp_tableを作成しなくてもOKではあるんですが、こういう方法もあるんですよ程度で聞いていただければ！