複数テーブルが絡む複雑なUPDATE文の書き方

質問

こんばんは

次のような2つのテーブルを参照して条件を指定するUPDATE文はどのように書けばよいのでしょうか?

Parentsテーブル

ID, Name, HasChild, AGE
1, Tanaka, True, 20
2, Yamamoto, False, 25
3, Suzuki, True, 30
4, Kobayashi, True, 20
5, Sato, True, 25

Children
ID, Name, ParentID, Male, Flag
1, Taro, 1, True, 0
2, Hanako, 1, False, 0
3, Ichiro, 3, True, 0
4, Jiro, 4, True, 0
5, Subroh, 5, True, 0

やりたいこと：20歳の親を持つ男の子のみにフラグを付ける

Parentテーブルで、親が子持ち（HasChild = True）で、年齢が20歳の人だけを対象に
Childrenテーブルでその親の子（Parents.ID = Children.ParentID）が男の子（Male = True)だった場合、
Flag を 0 から 1 に変更する

実行結果

Children
ID, Name, ParentID, Male, Flag
1, Taro, 1, True, 1
2, Hanako, 1, False, 0
3, Ichiro, 3, True, 0
4, Jiro, 4, True, 1
5, Subroh, 5, True, 0

結果は、Tanaka TaroくんとKobayashi Jiroくんのフラグが1になります。

複数テーブルのSELECTについては勉強してきましたが、こういうパターンは初めてでわかりません。エクセルVBAから.mdbファイルを扱う時に使います。Microsoft.Jet.OLEDB.4.0のADODB.Connectionを使っています。

よろしくお願いします。

回答No.1

piroin654 · ベストアンサー

ExcelからAccessのテーブルを更新するということならば
以下のような感じですか。Accessで更新クエリを作ってみれば
わかりますが。

Sub test()
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim strSQL As String
Dim strPath As String

strSQL = "UPDATE Childrenテーブル INNER JOIN Parentsテーブル " & _
"ON Childrenテーブル.ParentID = Parentsテーブル.ID SET Childrenテーブル.Flag = 1 " & _
"WHERE ([Parentsテーブル]![HasChild])=True AND ([Parentsテーブル]![AGE])=20 AND ([Childrenテーブル]![Male])=True"
strPath = ThisWorkbook.Path & "\sample.mdb"
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath

Set cmd.ActiveConnection = cn

With cmd
　　　　.CommandText = strSQL
　　　　.CommandType = adCmdText
　　　　.Prepared = True
End With

cmd.Execute
Set cmd = Nothing
cn.Close: Set cn = Nothing
End Sub

なお、SQL文の中身は

UPDATE Childrenテーブル INNER JOIN Parentsテーブル ON Childrenテーブル.ParentID = Parentsテーブル.ID SET Childrenテーブル.Flag = 1
WHERE ([Parentsテーブル]![HasChild])=True AND ([Parentsテーブル]![AGE])=20 AND ([Childrenテーブル]![Male])=True;

ですので、コード表が乱れていたら訂正してください。

お礼

夜分にありがとうございます。
さらにVB部分のスクリプトまで・・・感激しました。

SQL文を当てはめてみたところ、正常に動作しました！
[]なし、!の代わりに.として動きました。

UPDATEの後に2つのテーブルとJOIN位置を指定し、次に更新内容、最後に条件を指定すればいいんですね。
複雑に思えていた構文がすっきりと理解できました。これなら応用も利きそうです。