同じテーブルのフィールドを使ったUPDATEしたい

質問

こんにちは。
いつも参考にさせていただいています。

本日は投稿側です。

やりたいことは「T_ALL」テーブルの加工した「受付番号」フィールドを
「お客様番号」フィールドに書き込むことです。

ACCESS VBAで以下のコードを書き、実行すると[受付番号]のとこで
「定義されていない」とエラーになります。

思考錯誤しましたが解決できず困っています。
ご教示ください。

よろしくお願いします。

*************************************************************************
Dim db As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mypath, we As String
Dim mysql As String

Set db = New ADODB.Connection
Set rs = New ADODB.Recordset

db.Open "Provider=OraOLEDB.Oracle;Data Source=kcwf;User Id=kcwf;Password=kcwf;"
rs.Open "SELECT * FROM T_ALL", db, adOpenDynamic, adLockOptimistic, adCmdText

'***** 項目を更新
mysql = "UPDATE T_ALL SET お客様番号= 'B" & Mid([受付番号], 5, 9) & "'"
db.Execute mysql

'Close
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
*************************************************************************

回答No.1

piroin654 · ベストアンサー

mysql = "UPDATE T_ALL SET T_ALL.お客様番号= 'B' & Mid([受付番号],5,9)"

では？

補足

piroin654さま

ご回答ありがとうございます。

ご教示いただいた構文で実行すると「ORA-00933 SQLコマンドが正しく終了されていません」
となりました。

何度もすみません。
よろしくお願いします。

回答No.8

piroin654

>レコードが結構、多いものですので。

たしかに、レコードセットに対するループ処理は
時間がかかります。数十万件でもAccessに入れられる
データ量ならば一旦、Accessに実データを入れて、
変換処理をして、オラクルに変換したデータをそのまま
更新クエリなり、新しいテーブルに追加、あるいは
テーブル作成でもしたほうが早いかもしれません。

お礼

ありがとうございます。

"UPDATE T_ALL FROM set n='B" & mid(rs("受付番号"),5,9) & "';"
これで実装できることがわかりました。

色々とご教示いただきありがとうございました。

回答No.7

MARU4812

>"UPDATE T_ALL SET お客様番号 = ('B'|| Mid(受付番号,5,9));"
>で実行すると「文字が無効です」とエラーが出ました。
Oracle に Mid 関数がないみたいですね。
Access ではあります。

Oracle では
Substr([対象文字列],[開始位置],[切り取り長])

で代用するようです。

> Loopで行います。
それで仕様を満たしているうちは構いませんが、ローカルの
変更で一番問題になるのは速度です。1行1行コマンドを
投げる事になりますので、件数が増えた時はデータベースに
1命令だけ投げて更新するように求められる時が来るでしょう。

お礼

ありがとうございます。

もう少し色々、試してみたところ
"UPDATE T_ALL FROM set n='B" & mid(rs("受付番号"),5,9) & "';"
これで実装できました。

勉強させていただきありがとうございました。

回答No.6

piroin654

No4の、

rs.Fields("お客様番号") = "B" & Mid(rs.Fields("受付番号"), 5, 9)

を、No2のように

rs.Fields("お客様番号") = "B" || Mid(rs.Fields("受付番号"), 5, 9)

でもためしてみてください。

補足

ありがとうございます。

rs.Fields("お客様番号") = "B" & Mid(rs.Fields("受付番号"), 5, 9)

でうまく更新できました。

Loopではしっかり更新されるようです。

回答No.5

MARU4812

> ちなみにですが・・・
> mysql = "UPDATE T_ALL SET T_ALL.お客様番号= 'B" & Mid(受付番号,5,9) & "'"
それは、AccessVBA で「受付番号」という変数に入っているものを１つだけ処理し、
データベースに渡します。
例えば、AccessVBA で受付番号が"123456789"という文字列なら、
Mid(受付番号,5,9)は、"56789"
mysqlとしてデータベースに渡るのは "UPDATE T_ALL SET T_ALL.お客様番号= 'B56789'"
という文字列で、
★★★お客様番号= 'B56789'で全レコードが処理されます。★★★

> 動くのですが結果は「B」しかUPDATEされておらず、
AccessVBA で「受付番号」という変数がなかった場合、Option Explicit と書いておかないと
自動で変数が用意されてしまいます。中身は空っぽですので
受付番号が""という空文字列を用意したのと同じ意味です。
Mid(受付番号,5,9)は、""　(エラー出ないんですね。。。)
mysqlとしてデータベースに渡るのは "UPDATE T_ALL SET T_ALL.お客様番号= 'B'"
という文字列で、
★★★お客様番号= 'B'で全レコードが処理されます。★★★

どこが AccessVBA で処理されて、どこがデータベース(Oracle)で処理されるか
理解して下さい。
AccessVBA で文字列の連結は &(アンド)ですが、データベース(Oracle)では||(これもアンド)
です。

で、あとは順番に SQL を確認していくだけです。

上記のように UPDATE 文では被害が大きいです。そもそもデータベース操作に
バックアップは必須ですが大丈夫でしょうか？こんな質問をしているレベルの人に
データベースを操作させたら、テーブルのデータを壊すのは確実ですから、私なら
損害賠償の手はずを整えておきますがねｗ

素人がSQLを確認するなら、まずSELECT文でしょう。
例えば、
"SELECT [お客様番号], [受付番号], ('B'|| Mid([受付番号], 5, 9)) AS 加工受付番号 FROM T_ALL;"
ではどうですか？まずは、条件をちゃんと書けているか確認して下さい。
通らないなら、
"SELECT [お客様番号], [受付番号], Mid([受付番号], 5, 9) AS 加工受付番号 FROM T_ALL;"
"SELECT [お客様番号], [受付番号], (Mid([受付番号], 5, 9)) AS 加工受付番号 FROM T_ALL;"
"SELECT [お客様番号], [受付番号] FROM T_ALL;"
基本文法を確認して、確実に実行できるものまで遡ってから少しずつ複雑にしてください。
演算子の優先順は理解していますか？していないなら括弧を利用して確実な確認をしてください。
関数の使い方は合っていますか？結果も目で確認しましたか？

こういった確認が自分で出来れば、他人に質問する必要などないレベルの質問です。

最終的には
"UPDATE T_ALL SET T_ALL.お客様番号 = ('B'|| Mid(受付番号,5,9));"
でいけそうな気はしますが、テストはしてません。

補足

ありがとうございます。

すみません。ちょっと間違えておりました。
mysql = "UPDATE T_ALL SET T_ALL.お客様番号= 'B" & Mid(受付番号,5,9) & "'"
ではなくて
mysql = "UPDATE T_ALL SET お客様番号= 'B" & Mid(受付番号,5,9) & "'"
でした。結果は同じだったのですが。

>>どこが AccessVBA で処理されて、どこがデータベース(Oracle)で処理されるか理解して下さい。
>>AccessVBA で文字列の連結は &(アンド)ですが、データベース(Oracle)では||(これもアンド)です。
仰るとおりです。知識が乏しく失礼しました。

>>上記のように UPDATE 文では被害が大きいです。
>>そもそもデータベース操作にバックアップは必須ですが大丈夫でしょうか？
>>こんな質問をしているレベルの人にデータベースを操作させたら、
>>テーブルのデータを壊すのは確実ですから、
>>私なら損害賠償の手はずを整えておきますがねｗ
本テーブルはバックアップをとって。
ワークテーブルを使っておりますので大丈夫です。
それにご質問させて頂いているものが損害賠償に関わるような物ではありませんので。

最終的には
"UPDATE T_ALL SET お客様番号 = ('B'|| Mid(受付番号,5,9));"
で実行すると「文字が無効です」とエラーが出ました。

Loopで行います。

回答No.4

piroin654

mysql = "UPDATE T_ALL SET お客様番号= 'B" & Mid([受付番号], 5, 9) & "'"
db.Execute mysql

のかわりに、

Do While Not rs.EOF
rs.Fields("お客様番号") = "B" & Mid(rs.Fields("受付番号"), 5, 9)
rs.Update
rs.MoveNext
Loop

では、更新できますか？

補足

ありがとうございます。
Loopでは更新を確認しております。

最初に
"UPDATE T_ALL SET 区分= 'A'"
と
Do Until rs.EOF
rs!区分 = "A"
rs.Update
rs.MoveNext
Loop
の両方を試した時にUPDATEの方がパフォーマンスとして処理速度が速かったので今回、こちらでご質問させていただきました。
レコードが結構、多いものですので。

回答No.3

piroin654

No2の前に、以下のように末尾にセミコロンをつけてみてください。

mysql = "UPDATE T_ALL SET T_ALL.お客様番号= 'B' & Mid([受付番号],5,9);"

補足

ありがとうございます。

No２を実行すると「ORA-00936 式がありません」
No３を実行すると質問と同じエラーがでてしまいます。

ちなみにですが・・・
mysql = "UPDATE T_ALL SET T_ALL.お客様番号= 'B" & Mid(受付番号,5,9) & "'"
で実行すると動くのですが結果は「B」しかUPDATEされておらず、値を持ってこれてない状態になります。

度々、すみません。
よろしくお願いします。

回答No.2

piroin654

接続先はオラクルですか？

＞mysql = "UPDATE T_ALL SET T_ALL.お客様番号= 'B' & Mid([受付番号],5,9)"

mysql = "UPDATE T_ALL SET T_ALL.お客様番号= 'B' || Mid([受付番号],5,9)"

ですか？