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

SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説
最終更新日:2019-12-17

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


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


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

データベースの正規化とマスタの作成
エクセルVBAでデータベースを扱うためのSQL入門です。前回までは1つのテーブルのみを扱ってきましたが、これからは複数のテーブルを扱っていきます。複数テーブルの情報を必要に応じて結合して取得できるようになると、よりデータベースおよびSQLの利便性が感じられると思います。
ここでは、シートを経由してデータを別のテーブルにINSERTしましたが、
実は、SQLで他のテーブルから直接INSERTすることができます。
これについては、後々に紹介します。

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


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

全テーブル定義とテーブル自動作成VBA
データベースの正規化とマスタの作成で作成した全テーブル定義と、テーブル名変更と列追加(ALTERTABLE)とテーブル自動作成で作成したテーブル自動作成VBAを掲載しておきます。エクセルVBAでデータベースを扱うためのSQL入門です。前回までにテーブルを作成したり、削除したりできるようになりました。

新規テーブルを追加

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

VBA マクロ 他のテーブル INSERT UPDATE DELETE

以下で作成したVBAを使用します。
テーブル名変更と列追加(ALTER TABLE)とテーブル自動作成
エクセルVBAでデータベースを扱うためのSQL入門です。前回までにテーブルを作成したり、削除したりできるようになりました。今回は作成したテーブルの名称変更とカラム(列)を追加するSQLを解説します。そして、エクセルのシートにテーブル情報を記入して、そこからテーブルを自動作成するVBAも紹介します。
上記ページの自動作成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分が数秒に短縮されます。


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

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句のリストして使われます。
そして、そのリストに存在するか否かでテーブルを削除しています。

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

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

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句


新着記事NEW ・・・新着記事一覧を見る

ツイッターで出されたVBAのお題をやってみた|エクセル雑感(1月13日)
イベントプロシージャーの共通化(Enter,Exit)|ユーザーフォーム入門(1月13日)
Rangeオブジェクトの論理演算(差集合と排他的論理和)|VBA技術解説(1月10日)
イベントプロシージャーの共通化|ユーザーフォーム入門(1月7日)
コントロールの動的作成|ユーザーフォーム入門(1月6日)
Evaluateメソッド(文字列の数式を実行します)|VBA技術解説(1月5日)
エクスポート(PDF/XPS)|VBA入門(1月2日)
分析関数(OVER句,WINDOW句)|SQL入門(12月25日)
取得行数を限定するLIMIT句|SQL入門(12月21日)
外部ライブラリ(ActiveXオブジェクト)|VBA入門(12月21日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.マクロって何?VBAって何?|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ひらがな⇔カタカナの変換|エクセル基本操作




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


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



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