他のテーブルのデータで追加/更新/削除

公開日：2019-12-08　最終更新日：2019-12-17

他のテーブルのデータで追加/更新/削除

エクセルVBAでデータベースを扱うためのSQL入門です。
前回までに、データの追加/更新/削除を解説しました。
・INSERT
・UPDATE
・DELETE
ただし、全て単一のテーブルだけで完結しているもので、データはエクセルのシートにあるデータを使うものでした。

マスタ作成時の記事の最後には、以下のように紹介しました。

データベースの正規化とマスタの作成

・データベース正規化とは・マスタデータとトランザクションデータ・正規化したテーブル定義・非正規化から正規化したテーブルを作成・データベース正規化の最後に

ここでは、シートを経由してデータを別のテーブルにINSERTしましたが、
実は、SQLで他のテーブルから直接INSERTすることができます。
これについては、後々に紹介します。

今回は、他のテーブルのデータを使って、直接データの追加/更新/削除を行います。
さらに、インデックスの作成についても解説します。

使用するテーブル定義は以下になります。

全テーブル定義とテーブル自動作成VBA

・テーブル：m_customer ・テーブル：m_item ・テーブル：t_sales ・テーブル自動作成

新規テーブルを追加

以降で解説するSQLで使用するテーブルを新規に作成します。
code、sales_dateごとに金額を集計したテーブルを作成します。

以下で作成したVBAを使用します。

テーブル名変更と列追加（ALTER TABLE）とテーブル自動作成

・テーブル名変更：ALTER TABLE RENAME TO ・カラム（列）追加：ALTER TABLE ADD COLUMN ・SQLの半角空白と改行とセミコロン・テーブル自動作成・テーブル名変更と列追加とテーブル自動作成の最後に

上記ページの自動作成VBAを使用すると、以下のSQLを自動作成しテーブルが自動作成されます。

CREATE TABLE t_sales_date (
 "code" TEXT NOT NULL
,"sales_date" TEXT NOT NULL
,"item_amount" INTEGER
,PRIMARY KEY (code,sales_date)
);

他のテーブルを基にデータを追加

通常のINSERT

INSERT INTO テーブル
[ (カラム1 , カラム2 , ・・・) ]
VALUES
(値1 , 値2 , ・・・ )

他テーブルのSELECT結果をINSERTする場合のSQL構文

INSERT INTO テーブル
[ (カラム1 , カラム2 , ・・・) ]
SELECT [ カラム1 , カラム2 , ・・・ ] FROM 他テーブル・・・

SELECTした結果の全データがテーブルにINSERTされます。

INSERTするカラムとSELECTするカラムは一致している必要があります。
テーブル名の後のカラム名は()括弧で囲みます。

テーブルの全列をINSERTする場合はINSERTするカラム指定を省略できますが、
全く同一定義のテーブル同士以外では使用する機会は無いでしょう。

code、sales_dateごとに金額を集計してt_sales_dateにINSERTします。

INSERT INTO t_sales_date
 (code,sales_date,item_amount)
 SELECT
 code,sales_date
,SUM(item_price * item_count) AS item_amount
 FROM t_sales
 GROUP BY code,sales_date

SELECTは普通にSQLを作成すれば問題ありません。
SELECT以降の部分だけを抜き出して実行することができますので、
SELECT単体として実行して結果の確認を行ってから実際にINSERTすると良いでしょう。

他のテーブルを基にデータを更新

通常のUPDATE

UPDATE テーブル名
SET カラム名 = 値 [, カラム名 = 値 [, カラム名 = 値・・・]
[ WHERE 論理式 ]

他テーブルの値でUPDATEする場合のSQL構文

他のデータベースでは、UPDATEにFROMを指定できる構文があります。

UPDATE テーブル名
SET カラム名 = 値 [, カラム名 = 値 [, カラム名 = 値・・・]
[ FROM 他のテーブル]
[ WHERE 論理式 ]

さらに一部のデータベースでは、以下の構文も使用できます。

UPDATE テーブル名,他のテーブル
SET カラム名 = 他のテーブル.カラム名 [, カラム名 = 他のテーブル.カラム名・・・]
[ WHERE 論理式 ]

しかしSQLiteでは、いずれの構文も使えません。
SQLiteでは以下の構文を使ってください。

UPDATE テーブル名
SET カラム名 = (SELECT カラム名 FROM 他テーブル)
[ , カラム名 = ・・・]
[ WHERE 論理式 ]

この構文は、他のデータベースでも使用できるものです。
ただし、このSQLは極めて効率の悪く処理時間のかかるSQLとなっています。

SELECT部分はサブクエリ（副問合せ）と呼ばれるものになります。
サブクエリについては、次回以降で詳しく解説します。

code、sales_dateごとに金額を集計してt_sales_dateのitem_amountを更新します。

先のINSERTした後にt_salesに変更があった時、その変更を含めて全件UPDATEします。

UPDATE t_sales_date SET
 item_amount = 
(SELECT
 SUM(item_price * item_count) AS item_amount
 FROM t_sales T
 WHERE t_sales_date.code = T.code
   AND t_sales_date.sales_date = T.sales_date
 GROUP BY code,sales_date)
 WHERE code = '001'

先にも書きましたが、このSQLは極めて効率の悪く処理時間のかかるSQLとなっています。
そこで、最後の「WHERE code = '001'」これで更新対象を絞り込んでいます。
t_salesには'001'が約1万件あり、この処理に3秒程度かかりました。
全100万件の場合は、約5分かかっています。
これは、DBの処理としては異例とも言えるほど遅いものです。

この時間は、テーブルのインデックスを作成することで劇的に速くできます。
次回、「インデックスを作成して高速化（CREATE INDEX）」で紹介します。
インデックスを作成することで、約5分が数秒に短縮されます。

UPDATEは、データベースにより使える構文が変わります。
各デーベースごとに、使える構文を確認してください。

他のテーブルを基にデータを削除

通常のDELETE

DELETE FROM テーブル名
[ WHERE 論理式]

他テーブルを基にDELETEする場合のSQL構文

一部のデータベース特有の構文はありますが、広くサポートされている構文はありません。
USING句で結合して削除できるデーベースもありますが、USINGの指定方法がそれぞれに違っていたりします。
さらに、JOINで結合して削除できるものもありますが、特定のDBだけになります。

少なくともSQLiteには特段の構文が存在しません。
データベースごとに、SQLの書き方が複数存在しますので、データベースごとに確認してください。

以下の構文であれば、大抵のデーベースで使用できます。

DELETE FROM テーブル名
WHERE 式 [NOT] EXISTS IN
(SELECT 式 FROM 他のテーブル　WHERE ・・・)

EXISTS演算子

EXISTSは、その後のSELECT結果に行が存在する場合に真を返します。
NOT演算子を付けることで、存在しない場合となります。
SELECT部分はサブクエリ（副問合せ）と呼ばれるものになります。
サブクエリについては、次回以降で詳しく解説します。

IN演算子

式 IN (式, ・・・)
INの前の式が、()内のいずれかと一致している場合に真

SELECTした結果の値がIN句のリストして使われます。
そして、そのリストに存在するか否かでテーブルを削除しています。

この方法であれば、データベースの種類を気にする必要はないでしょう。
しかし他の方法がサポートされているデータベースでは、処理速度や効率を考慮して検討してください。

t_salesに存在しないcode,sales_dateは、t_sales_dateから削除する。

code,sales_dateを文字列結合して比較します。

DELETE FROM t_sales_date
 WHERE
 code || sales_date NOT IN 
(SELECT code || sales_date
 FROM t_sales
 GROUP BY code,sales_date)

SELECTで、t_salesをcode,sales_dateでグループし、code,sales_dateの文字列結合した結果を返します。
この結果のt_sales||code,sales_dateこれがt_sales_dateに存在しない場合にDELETEしています。

この場合は、code,sales_dateともに文字列型なので単純に結合しましたが、
数値型の場合等、データによっては単純結合では意図しない一致が発生することもありえます。
1||111これと11||11これら一致してしまいます。
途中に区切り文字を加える等の工夫をする必要がある場合もあります。

この方法は汎用性も拡張性もあり便利な使い方だと思います。
その反面あまり効率的なSQLとは言えませんので、他の方法が存在するデータベースではいろいろ検討してみてください。

他のテーブルを基にデータを追加/更新/削除の最後に

他のテーブル情報を使って、INSERT/UPDATE/DELETEを紹介しました。
UPDATEとDELETEは、デーベースごとにサポートされている構文が大分違いますので、それぞれで確認してください。

UPDATEとDELETEで紹介した方法の中に記載しましたが、サブクエリ（副問合せ）を使用しています。
サブクエリ（副問合せ）についてはもう少し後で詳しく解説していきます。
サブクエリこそ、SQLの真骨頂と言えるものです。

UPDATEで多大な時間がかかってしまい、そこでインデックスについて説明しました。
次回は、インデックスを作成して今回のUPDATEを高速化します。
インデックスを作成することで、約5分が数秒に短縮できます。

同じテーマ「SQL入門」の記事

複数のSELECT結果を統合（UNION,UNION ALL）
データの更新（UPDATE）
データの削除（DELETE）
他のテーブルのデータで追加/更新/削除
インデックスを作成して高速化（CREATE INDEX）
トランザクション処理
 VBAクラスの全コード：トランザクション処理
 サブクエリ（副問合せ）
サブクエリのネストとSQLコメント&整形
 WITH句（共通テーブル式）
取得行数を限定するLIMIT句

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
その他
SQL入門
他のテーブルのデータで追加/更新/削除

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

SQL入門
他のテーブルのデータで追加/更新/削除