SQL入門
データの更新(UPDATE)

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

データの更新(UPDATE)


エクセルVBAでデータベースを扱うためのSQL入門です。
前回までにやってきたことを大きく分けると、
・テーブル作成
・データ挿入
・データ取得
データベースにテーブルを作成し、データを入れて、そしてデータを取り出しました。
これでデータベースを最低限扱う事ができるようになりましたが、
一度入れたデータを変更したり、削除したりできないと困ってしまいます。


データの更新は、UPDATEです。
データの削除は、DELETEです。
今回は、UPDATEについて解説します。

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

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

UPDATATEの構文

データベースの既存データを更新するには、UPDATEを使用します。

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

値は、定数値または各種の式を指定します。

UPDATAEに続けて更新するテーブル名を記述します。
SETの後には、更新するカラムとその値をペアにして,で区切って複数指定できます。
WHERE句を省略すると、テーブルの全データが更新されます。

UPDATEの使用例

m_customerのnameの値を全行更新します。

UPDATE m_customer 
 SET name = '仕入先'||SUBSTR(name,LENGTH(name)-2)
以前のテストデータ作成で、
nameには、'販売先001'
このように、'販売先'+3桁数値 を入れました。
このSQLでは、'仕入先'+3桁数値 に更新しています。
'販売先001'のLEMGTH=6、6-2=4、4文字目から後ろが数値部分になります。

このSQLを実行することで、m_customerの全行が、
'販売先nnn' → '仕入先nnn' と更新されます。

文字列SQL関数
上記の文字列処理は、REPLACE関数でも同じことができます。
むしろ、これだけであれば、REPLACE関数を使うのが普通でしょう。
上記では、関数のネストの紹介も兼ねて記述しました。



関数名 構文 説明
LENGTH LENGTH(文字列式) 文字列の文字数を返します。
バイト数ではなく、文字数です、漢字も1文字となります。
※DBによってはバイト数を返します。
REPLACE REPLACE(文字列式, 置換前, 置換後) 文字列の置換前を置換後に置き換えて返します。
大文字・小文字は区別されます。
SUBSTR SUBSTR(文字列式, 開始位置, 文字数) 文字列の開始位置から文字数分の文字列を返します。
文字数を省略した場合は文字列の最後までが対象となります。
※DBによっては関数名がSUBSTRINGになります。
※DBによっては文字数ではなくバイト数指定になります。

SELECTで取得→シートで編集→UPDATEで更新

以下VBAは、こちらのクラスを使用したVBAサンプルになります。
VBAクラスの全コード:データの取得
SQL入門の「データの取得:条件指定(SELECT,WHERE)」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コード クラスモジュール:clsSQLite

Sub SelectALL()
  Dim clsDB As New clsSQLite
  clsDB.DataBase = "C:\SQLite3\sample.db"
 
  Dim ws As Worksheet
  Set ws = ActiveSheet
 
  Dim sSql As String
  sSql = ""
  sSql = sSql & "SELECT *"
  sSql = sSql & " FROM m_customer"
  sSql = sSql & " ORDER BY code"
  
  If Not clsDB.SheetFromRecordset(sSql, ws.Range("A1"), Clear, True) Then
    MsgBox clsDB.ErrMsg
    Exit Sub
  End If
 
  Set clsDB = Nothing
End Sub

m_customerの全データをシートに出力しています。

VBA マクロ SQL UPDATAE

更新したいデータ行だけにします。
もちろん、そのまま全行残しても構いません。
変更しなければ、同じデータで更新することになるだけです。
(変更していないのにデータベースを更新することは、本来は避けるべきです。)

A列のcodeは変更してはダメです。
codeは主キー(PRIMARY KEY)なので、今回の場合は変更は不可です。
キーが変わってしまったら更新になりませんので。
ここではあくまで、UPDATEのサンプル&テストのため、簡易的な作りにしています。

VBA マクロ SQL UPDATAE

以下のVBAについて
A列が主キーとして固定のロジックにしています。
・B列以降がデータとして列数の変動に対応しています。
・日付、数値、文字列は自動判別しています。
・文字列の ' は、 '' でエスケープ処理しています。


Sub SelectUpdate()
  Dim clsDB As New clsSQLite
  clsDB.DataBase = "C:\SQLite3\sample.db"
 
  Dim sSql As String
  Dim i As Long, j As Long
  Dim maxRow As Long, maxCol As Long
  Dim ws As Worksheet
  Set ws = ActiveSheet
  With ws
    maxRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    maxCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    For i = 2 To maxRow
      sSql = ""
      sSql = sSql & "UPDATE m_customer SET" & vbCrLf
      For j = 2 To maxCol
        sSql = sSql & IIf(j = 2, " ", ",")
        sSql = sSql & .Cells(1, j)
        sSql = sSql & " = "
        sSql = sSql & getValue4Sql(.Cells(i, j)) & vbCrLf
      Next
      sSql = sSql & " WHERE "
      sSql = sSql & .Cells(1, 1)
      sSql = sSql & " = "
      sSql = sSql & getValue4Sql(.Cells(i, 1))
      If Not clsDB.ExecuteNonQuery(sSql) Then
        MsgBox clsDB.ErrMsg
        Exit Sub
      End If
    Next
  End With

  Set clsDB = Nothing
End Sub

'SQL用にセルのValueを編集
Function getValue4Sql(ByVal aRange As Range) As String
  Select Case TypeName(aRange)
    Case "Date"
      getValue4Sql = dateFormat(aRange.Value)
    Case "Double"
      getValue4Sql = aRange.Value
    Case Else
      getValue4Sql = addQuote(aRange.Value)
  End Select
End Function

'SQLiteは日付型がないので文字列として格納
Function dateFormat(ByVal var As Variant) As String
  dateFormat = addQuote(Format(var, "yyyy-mm-dd"))
End Function

'シングルクオーテーションをエスケープ処理
Function addQuote(ByVal str As String, _
         Optional ByVal aQuote As String = "'") As String
  If str = "" Then
    addQuote = "NULL"
  Else
    addQuote = aQuote & Replace(str, "'", "''") & aQuote
  End If
End Function

このVBAの、SelectUpdate を実行すると、以下のようなSQLが行数分発行されます。

UPDATE m_customer SET
 name = '仕入先003a'
,address = '東京'
 WHERE code = '003'

VBA実行が終了したら、再度最初のデータ取得、
SelectALL これを実行してデータが更新されたことを確認します。

VBA マクロ SQL UPDATAE

データの更新(UPDATE)の最後に

今回はUPDATEの学習・練習として、極めて簡単な例、および単純なVBAにしています。
実務的には、データの更新は様々な注意点が出てくるものです。
主キーの変更を許可するかどうか、許可するなら旧のキーを削除するかどうか等々
このような細部の仕様を決めないと簡単には更新ロジックは作成できません。
SQL入門でも、先々にはそのようなVBAも紹介できればと思っていますが、今はまだSQLの基本解説を優先しています。

次回はデータ削除、DELETEについて解説します。



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

全テーブル定義とテーブル自動作成VBA
テーブルを結合して取得(INNER JOIN,OUTER JOIN)
複数のSELECT結果を統合(UNION,UNION ALL)
データの更新(UPDATE)
データの削除(DELETE)
他のテーブルのデータで追加/更新/削除
インデックスを作成して高速化(CREATE INDEX)
トランザクション処理
VBAクラスの全コード:トランザクション処理
サブクエリ(副問合せ)
サブクエリのネストとSQLコメント&整形


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

VBA100本ノック 18本目:名前定義の削除|VBA練習問題100(11月6日)
VBA100本ノック 17本目:重複削除(ユニーク化)|VBA練習問題100(11月6日)
VBA100本ノック 16本目:無駄な改行を削除|VBA練習問題100(11月5日)
VBA100本ノック 15本目:シートの並べ替え|VBA練習問題100(11月4日)
VBA100本ノック 14本目:社外秘シート削除|VBA練習問題100(11月3日)
VBA100本ノック 13本目:文字列の部分フォント|VBA練習問題100(11月1日)
VBA100本ノック 12本目:セル結合を解除|VBA練習問題100(10月31日)
VBA100本ノック 11本目:セル結合の警告|VBA練習問題100(10月30日)
VBA100本ノック 10本目:行の削除|VBA練習問題100(10月29日)
VBA100本ノック 9本目:フィルターコピー|VBA練習問題100(10月28日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
5.マクロって何?VBAって何?|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




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


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



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