【access】表の更新（UPDATE文）

質問

accessでの表の更新に関しご教示ください。

access2010を使用しTBLを結合後、更新を行いたいです。
具体的には以下の通り。

■仕様
TBL1（ローカルテーブ）とTBL2（リンクテーブル）をKeyで結合。
TBL1.M1がNullの項目に対し、TBL2.Numberをセットしたい。

TBL1（全てテキスト型）
――――――
Key ｜M1
――――――
1　 ｜A123
――――――
2　 ｜
――――――
3　 ｜C123

TBL2（全てテキスト型）
――――――
Key ｜Number
――――――
1　 ｜A123
――――――
2　 ｜B123
――――――
3　 ｜C123

■実行結果が上手くいかなかったSQL
UPDATE TBL1 as T1 INNER JOIN TBL2 as T2 ON T1.Key = T2.Key
SET T1.M1 = T2.Number
WHERE T1.M1 Is Null OR T1.M1 LIKE "*";

回答No.3

山田 太郎（@f_a_007） · ベストアンサー

【訂正】No.2さんの回答を見て・・・

あちゃー！

UPDATE TBL2 INNER JOIN TBL1 ON TBL2.Key=TBL1.Key
SET TBL2.M1 = TBL1.M1
WHERE LEN(TBl2.M1 & "")=0;

INNER JOIN しているので WHERE節は《ヌルならば》で事足りることを失念し無駄な条件を書いていることに気が付きました。当然に「あっ、そうなんだ！」とは悟られたでしょうが・・・。ここに、明示的に訂正しておきます。なお、IsNUll（）ではなくてLEN()を用いる場合は

LEN(TBl2.M1=0;

は、エラーになります。

LEN(TBl2.M1 & "")=0;

とヌル値対策が必要です。

お礼

ご丁寧にありがとうございました。
ご教示頂いた内容で上手くいきました。

回答No.4

chayamati

それぞれのテーブルのkeyが主キーまたは重複なしになっていることが前提です。
処理を間違えると逆方向にコピーされます、テーブルのバックアップしてください
添付のように更新クエリを使います。
1.クエリデザインからTBL1、TBL2の表示
2.TBL1.keyからTBL2.Keyへドラッグして関連付けします
3.空白部分右クリックからクエリの種類、更新と進んで更新行を表示させます。

あとは添付図を参考にして下さい

テーブル表示域の枠内の右クリックからSQIレビューで次のSQL文が表示されます

UPDATE TBL1 LEFT JOIN TBL2 ON TBL1.key = TBL2.key SET TBL1.M1 = [Number]
WHERE (((TBL1.M1) Is Null));

回答No.2

piroin654

Where句のところが少し残念ですが、他のところも
いろいろありますが、一応以下で動くと思います。

update TBL1 inner join TBL2 on TBL1.Key = TBL2.Key
set TBL1.M1 = TBL2.Number
where IsNull(TBL1.M1)

のように、
where IsNull(TBL1.M1)
で十分のはずですが。
IsNullは関数のIsNullを使いますので、Is　とNull　を離して表記はしません。

回答No.1

山田 太郎（@f_a_007）

Q、TBL2にTBL1をコピーするには？
A、SQL文が苦手であれば・・・

次のような手もあります。が、これが、仮に学校の宿題であれば、以下の手法はアウトです。

UPDATE TBL2 SET M1 = DBLookup("SELECT M1 FROM TBL1 WHERE Key=" & [Key]) AND LEN(TBL2.M1 & "")=0;

＊列名は、もちろんM1=>Numberに変更する必要があります。
＊回答では、TBL2を更新しています。TBL1でも同じこと。

要は、SQL文の実行結果を戻す関数を用意すれば、結合とか複文とかを考えなくても同じ結果を得ることができるということです。もちろん、邪道と言えば邪道。でも、結果オーライで良ければこれもありですね。

Public Function DBLookup(ByVal strQuerySQL As String, _
　　　　　　　　　　　　　Optional ByVal ReturnValue = Null) As Variant
On Error GoTo Err_DBLookup
　　Dim DataValue
　　Dim rst As ADODB.Recordset

　　Set rst = New ADODB.Recordset
　　With rst
　　　　.Open strQuerySQL, _
　　　　　　　CurrentProject.Connection, _
　　　　　　　adOpenStatic, _
　　　　　　　adLockReadOnly
　　　　If Not .BOF Then
　　　　　　.MoveFirst
　　　　　　DataValue = .Fields(0)
　　　　End If
　　End With
Exit_DBLookup:
On Error Resume Next
　　rst.Close
　　Set rst = Nothing
　　DBLookup = IIf(Len(DataValue & ""), DataValue, ReturnValue)
　　Exit Function
Err_DBLookup:
　　MsgBox "SELECT 文の実行時にエラーが発生しました。(DBLookup)" & Chr$(13) & Chr$(13) & _
　　　　　 "・Err.Description=" & Err.Description & Chr$(13) & _
　　　　　 "・SQL Text=" & strQuerySQL, _
　　　　　 vbExclamation, " 関数エラーメッセージ"
　　Resume Exit_DBLookup
End Function

※※※※※※※※※※※※※※※
　PS、本来のやり方
※※※※※※※※※※※※※※※

UPDATE TBL2 INNER JOIN TBL1 ON TBL2.Key=TBL1.Key SET TBL2.M1 = TBL1.M1
WHERE TBL2.Key=TBL1.Key AND LEN(TBL2.M1 & "")=0;

お礼

ご丁寧にありがとうございました。
こちらも試してみて上手くいきました。