最終更新日:2019-12-17他のテーブルのデータで追加/更新/削除エクセルVBAでデータベースを扱うためのSQL入門です。前回までに、データの追加/更新/削除を解説しました。・INSERT・UPDATE・DELETEただし、全て単一のテーブルだけで完結しているもので、データはエクセルのシートにあるデータを使うものでした。マスタ作成時の記事の最後には、以下のように紹介しました。データベースの正規化とマスタの作成エクセルVBAでデータベースを扱うためのSQL入門です。前回までは1つのテーブルのみを扱ってきましたが、これからは複数のテーブルを扱っていきます。複数テーブルの情報を必要に応じて結合して取得できるようになると、よりデータベースおよびSQLの利便性が感じられると思います。実は、SQLで他のテーブルから直接INSERTすることができます。これについては、後々に紹介します。今回は、他のテーブルのデータを使って、直接データの追加/更新/削除を行います。インデックスの作成についても解説します。使用するテーブル定義は以下になります。全テーブル定義とテーブル自動作成VBAデータベースの正規化とマスタの作成で作成した全テーブル定義と、テーブル名変更と列追加(ALTERTABLE)とテーブル自動作成で作成したテーブル自動作成VBAを掲載しておきます。エクセルVBAでデータベースを扱うためのSQL入門です。前回までにテーブルを作成したり、削除したりできるようになりました。以降で解説するSQLで使用するテーブルを新規に作成します。code、sales_dateごとに金額を集計したテーブルを作成します。以下で作成したVBAを使用します。TABLE)とテーブル自動作成エクセルVBAでデータベースを扱うためのSQL入門です。前回までにテーブルを作成したり、削除したりできるようになりました。今回は作成したテーブルの名称変更とカラム(列)を追加するSQLを解説します。エクセルのシートにテーブル情報を記入して、そこからテーブルを自動作成するVBAも紹介します。上記ページの自動作成VBAを使用すると、以下のSQLを自動作成しテーブルが自動作成されます。・・・SELECTした結果の全データがテーブルにINSERTされます。INSERTするカラムとSELECTするカラムは一致している必要があります。テーブル名の後のカラム名は()括弧で囲みます。同一定義のテーブル同士以外では使用する機会は無いでしょう。code、sales_dateごとに金額を集計してt_sales_dateにINSERTします。code,sales_dateSELECTは普通にSQLを作成すれば問題ありません。SELECT以降の部分だけを抜き出して実行することができますので、SELECT単体として実行して結果の確認を行ってから実際にINSERTすると良いでしょう。他のデータベースでは、UPDATEにFROMを指定できる構文があります。]さらに一部のデータベースでは、以下の構文も使用できます。]しかしSQLiteでは、いずれの構文も使えません。SQLiteでは以下の構文を使ってください。]この構文は、他のデータベースでも使用できるものです。このSQLは極めて効率の悪く処理時間のかかるSQLとなっています。SELECT部分はサブクエリ(副問合せ)と呼ばれるものになります。サブクエリについては、次回以降で詳しく解説します。code、sales_dateごとに金額を集計してt_sales_dateのitem_amountを更新します。先のINSERTした後にt_salesに変更があった時、その変更を含めて全件UPDATEします。'001'先にも書きましたが、このSQLは極めて効率の悪く処理時間のかかるSQLとなっています。'001'」これで更新対象を絞り込んでいます。t_salesには'001'が約1万件あり、この処理に3秒程度かかりました。全100万件の場合は、約5分かかっています。これは、DBの処理としては異例とも言えるほど遅いものです。この時間は、テーブルのインデックスを作成することで劇的に速くできます。INDEX)」で紹介します。インデックスを作成することで、約5分が数秒に短縮されます。SELECTは普通にSQLを作成すれば問題ありません。SELECT以降の部分だけを抜き出して実行することができますので、SELECT単体として実行して結果の確認を行ってから実際にINSERTすると良いでしょう。UPDATEは、データベースにより使える構文が変わります。各デーベースごとに、使える構文を確認してください。一部のデータベース特有の構文はありますが、広くサポートされている構文はありません。USING句で結合して削除できるデーベースもありますが、USINGの指定方法がそれぞれに違っていたりします。JOINで結合して削除できるものもありますが、特定のDBだけになります。少なくともSQLiteには特段の構文が存在しません。データベースごとに、SQLの書き方が複数存在しますので、データベースごとに確認してください。以下の構文であれば、大抵のデーベースで使用できます。・・・)EXISTS演算子EXISTSは、その後のSELECT結果に行が存在する場合に真を返します。NOT演算子を付けることで、存在しない場合となります。SELECT部分はサブクエリ(副問合せ)と呼ばれるものになります。サブクエリについては、次回以降で詳しく解説します。・・・)INの前の式が、()内のいずれかと一致している場合に真SELECTした結果の値がIN句のリストして使われます。そのリストに存在するか否かでテーブルを削除しています。SELECTは普通にSQLを作成すれば問題ありません。SELECT以降の部分だけを抜き出して実行することができますので、SELECT単体として実行して結果の確認を行ってから実際にINSERTすると良いでしょう。この方法であれば、データベースの種類を気にする必要はないでしょう。他の方法がサポートされているデータベースでは、処理速度や効率を考慮して検討してください。t_salesに存在しないcode,sales_dateは、t_sales_dateから削除する。code,sales_dateを文字列結合して比較します。code,sales_date)SELECTで、t_salesをcode,sales_dateでグループし、code,sales_dateの文字列結合した結果を返します。この結果のt_sales||code,sales_dateこれがt_sales_dateに存在しない場合にDELETEしています。数値型の場合等、データによっては単純結合では意図しない一致が発生することもありえます。1||111これと11||11これら一致してしまいます。途中に区切り文字を加える等の工夫をする必要がある場合もあります。この方法は汎用性も拡張性もあり便利な使い方だと思います。その反面あまり効率的なSQLとは言えませんので、他の方法が存在するデータベースではいろいろ検討してみてください。他のテーブルを基にデータを追加/更新/削除の最後に他のテーブル情報を使って、INSERT/UPDATE/DELETEを紹介しました。UPDATEとDELETEは、デーベースごとにサポートされている構文が大分違いますので、それぞれで確認してください。UPDATEとDELETEで紹介した方法の中に記載しましたが、サブクエリ(副問合せ)を使用しています。サブクエリ(副問合せ)についてはもう少し後で詳しく解説していきます。サブクエリこそ、SQLの真骨頂と言えるものです。UPDATEで多大な時間がかかってしまい、そこでインデックスについて説明しました。次回は、インデックスを作成して今回のUPDATEを高速化します。インデックスを作成することで、約5分が数秒に短縮できます。他のテーブルのデータで追加/更新/削除このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。他のテーブルのデータで追加/更新/削除このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。