データ補正に役立つ! Excelのセル機能を使ってSQL文を簡単に生成しよう
目次
こんにちは。篠原です。
今回は、データ補正作業の際に使っている便利な技を紹介します。一度覚えてしまえば、簡単にたくさんのSQL文を作れるようになるので、覚えておくと作業が捗ります。
システム上でデータの修正ができない場合は、SQLを使ってUPDATE文を流してデータの補正を行います。しかし、そのSQL文を毎度毎度、作成するのは非常に時間がかかってしまいます。
そこで、Excelのセル機能を使って、簡単に大量のSELECT文やUPDATE文を生成できるようにしておきましょう。
対象のテーブル
今回データ補正を想定するテーブルはこちら。セルB6の「order_id」が主キーになります。通販サイトをイメージしていただければわかりやすいかと思います。
SELECT文
まずは主キーの「order_id」を使って、データを抽出するSELECT文から作っていきましょう。作成するSQLは下記です。
SELECT *
FROM order_tb
WHERE order_id = ‘A1080731’;
ではセル機能を使って生成してみます。セルC2のテーブル名とセルB4のカラム名は、[F4] キー(ショートカット)を押して絶対参照にします。
セルの参照種類については、下記記事にて紹介しています。
Excelを使って業務効率化! 便利なExcel関数5選を紹介します
生成したSELECT文は、赤字のように表示されます。それではこのSELECT文を、10個分生成しましょう。1つ目のSELECT文を含めて、下に10行セルを選択し、[Ctrl] キー + [D] キーを押してセルをコピーします。
無事にSELECT文が10個生成されました。
注意点は、データ型が文字列の場合、SQLのルールとしてシングルコーテーションで囲う必要があります。テーブル表にはシングルコーテーションがついた値は入っていないので、セルの入力欄には「” = ‘“&B6&”‘ ;”」のように、シングルコーテーションを入れます。
UPDATE文
次はUPDATE文を生成します。先ほどと同様に主キーの「order_id」を使って、データの出荷フラグ、更新日時、更新IDをUPDATEするSQLを作っていきます。作成するSQLは下記です。
UPDATE order_tb
SET
delibery_flag = ‘1’,
update_date = ‘2022/12/21 00:00.00’,
update_id = ‘7FAED’
WHERE order_id = ‘A1080731’ ;
SELECT文と同じく、テーブル名とカラム名は、[F4] キーを押して絶対参照にします。
=”UPDATE “&$C$2&” SET “&$G$4&” = ‘”&G6&”‘, “&$J$4&” = ‘”&J6&”‘, “&$K$4&” = ‘”&K6&”‘ WHERE “&$B$4&” = ‘”&B6&”‘ ;”
INSERT文
次はINSERT文を生成します。INSERT文の場合は、テーブルにはないデータを追加するため、INSERTする値はすべて入力しておく必要があります。作成するSQLは下記です。
INSERT INTO order_tb
VALUES (
’A1080731′,
’406SY7′,
’ヤマダ タロウ’,
’2022/12/20′,
’東京都世田谷区’,
’1′,
’2022/12/20′,
’ULK8K’,
’2022/12/21′,
’7FAED’
) ;
では、セル機能を使って生成しましょう。セルの絶対参照を忘れずに!
=”INSERT INTO “&$C$2&” VALUES (‘”&B6&”‘, ‘”&C6&”‘, ‘”&D6&”‘, ‘”&E6&”‘, ‘”&F6&”‘, ‘”&G6&”‘, ‘”&H6&”‘, ‘”&I6&”‘, ‘”&J6&”‘, ‘”&K6&”‘ ) ;”
期待通りにSQLを作成できました!
DELETE文
最後はDELETE文です。保守作業ではあまり使わないのでおまけです。
DELETE FROM order_tb
WHERE order_id = ‘A1080731’ ;
セルの入力値はこちら
=”DELETE FROM “&$C$2&” WHERE “&$B$4&” = ‘”&B6&”‘ ;”
まとめ
以上になります。いかがでしたでしょうか。
SQL生成は自動化されていることも多いですが、どのように自動生成されているかを知ることが大事です。マクロを使って自動生成するパターンもあるので、今後そちらも勉強して紹介したいと思います。
《関連記事》