データの更新（UPDATE）

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

データの更新（UPDATE）

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

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

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

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

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

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の全データをシートに出力しています。

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

※A列のcodeは変更してはダメです。

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

以下の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 これを実行してデータが更新されたことを確認します。