Q&A
テーブルAは、
年月日：日付/時刻型、出勤時刻：時刻型、退勤時刻：時刻型ではなく、
年月日：日付型、出勤時刻：日付/時刻型、退勤時刻：日付/時刻型じゃないですか？
前提・実現したいこと
ODBCデータソースにMySQLのDBを登録し、Access2010にて、リンクテーブルで管理しているMySQL側のテーブル(仮にAとします）と、
別のAccessDBからのリンクテーブル（仮にBとします）を管理しています。
Bには1ヶ月間の出勤時刻データが格納されていて、毎日更新されます。
AはBの累積データが格納されています。
月初めにBからAに1ヶ月分のデータレコードを用意し、以後は毎日レコード更新という形で
Aのデータが更新されるように考えています。
テーブル構成は下記のとおりです。(項目省略しています)
A
年月日：日付/時刻型（主キー）
社員No：数値型（主キー）
勤務区分：テキスト型
出勤時刻：時刻型
退勤時刻：時刻型
…
B
年月日：日付/時刻型（主キー）
社員No：数値型（主キー）
勤務区分：テキスト型
出勤時刻：テキスト型
退勤時刻：テキスト型
※10:59訂正しました。B側の時刻データはテキスト型となっておりました。
発生している問題・エラーメッセージ
次のSQL文を実行してデータ更新を行っていますが、勤務区分をはじめ、他の数値・テキスト項目は
更新されているのですが、時刻の項目だけが更新されません。
SQL
1UPDATE A INNER JOIN B ON A.年月日=B.年月日 and A.社員No=B.社員No 2SET A.出勤時刻＝B.出勤時刻,A.退勤時刻=B.退勤時刻,A.勤務区分=B.勤務区分
何か原因として考えられるものがあればご教示下さい。
※10:59訂正により、テキスト型データを時刻型フィールドには代入できない？
試したこと
ODBCの設定より、「Return matched rows instead of affected rows」にチェックを入れています。(これが無いと、更新時にレコードのロック違反というエラーメッセージが表示され、勤務区分すらも更新されなかった。)
補足情報（FW/ツールのバージョンなど）
MySQL ODBC 8.0 Unicode Driver 8.00.12.00
Access2010 SP2
よろしくお願いいたします。
※17:23 画像削除させていただきました。
おっしゃる通りです。Accessでリンクテーブルのデザインで開くと日付/時刻型ですが、MySQL(phpMyAdmin)では、Time型となっています。
直接値を指定して更新されOKだったクエリーも追記して貰えますか。
UPDATE A INNER JOIN B ON (A.社員No = B.社員Ｎｏ) AND (A.年月日 = B.年月日) SET A.勤務 = B.勤務, A.出勤 = '11:00', A.退勤 = B.退勤
WHERE B.社員Ｎｏ=18895;
こちらで実行し、出勤時刻が全日において2019/04/08 11:00となりました。
回答2件
0
ベストアンサー
テキスト型である出勤時刻および退勤時刻はどのような書式で格納されていますか？
更新先が時刻型であるので、テキスト型でHHMMなど格納されているのであれば、HH:MMのように変換しないと駄目じゃないでしょうか。
但し、ODBCドライバーが暗黙変換してくれればの話ですので、駄目な場合は型を合わせる必要があると思います。
追記
SQL
1UPDATE A INNER JOIN B ON (A.社員No = B.社員Ｎｏ) AND (A.年月日 = B.年月日) 2SET A.勤務 = B.勤務, A.出勤 = '11:00', A.退勤 = B.退勤
これで問題ないという事なので、更新されているのかどうかをlastupdateを更新するようにしてみると更新されているかどうかが分かると思います。
SQL
1UPDATE A INNER JOIN B ON (A.社員No = B.社員Ｎｏ) AND (A.年月日 = B.年月日) 2SET A.勤務 = B.勤務, A.出勤 = B.出勤, A.退勤 = B.退勤, A.lastupdate=Now()
追記２
暗黙変換のせいかもしれませんので、以下を試してみてください
SQL
1UPDATE A INNER JOIN B ON (A.社員No = B.社員Ｎｏ) AND (A.年月日 = B.年月日) 2SET A.勤務 = B.勤務, A.出勤 = CStr(B.出勤), A.退勤 = CStr(B.退勤)
投稿2019/04/08 02:51
編集2019/04/08 08:31総合スコア25428
ご回答ありがとうございます。
Ｂテーブルの出勤時刻(テキスト型)は、定型入力=##:##としています。
変換を明示するべく、ACCESS側のSQLでCDate()でキャストするようにしてみましたが、うまくいきませんでした。
定型入力は格納されるデータそのものではありませんよ。
データの内容を確認して下さい。
また、データが格納されないのは型変換のエラーになっているはずですので、update文をクエリーで実行してみれば、詳細が分かると思います。
お恥ずかしながら、データそのものの確認はどうしたらできるのでしょうか。
私の認識としては、定型入力##:##としているので、データも例えば
12:34というテキストデータが格納されているものと考えていたのですが…。
Update文を更新クエリとして実行してみたところ、特にエラーなどは表示されませんでした。
テーブルを開いて12:34というテキストデータが格納されているのを確認されていますか？
また、更新クエリーでテーブルBを指定せず、直接値で更新するとどうなるでしょうか。
テーブルを開いたところ、12:34というデータでした。
直接値での更新('12:34'を指定)では更新できました。
また、不可思議なことにAテーブルの1ヶ月分のデータ(30件)のうち、1日と4日5日の出勤時刻がUpdate文実行後に消えてしまいました。もちろん、更新元データであるBの方にはちゃんとデータがあります。
元々、本稼働しているシステム環境からテスト環境を作って機能改善を図っている途中でこの現象に遭遇しています。
テスト環境を作る過程で何か問題が生じているのかもしれません。
事象からすると、別な処理で更新しているように見えますね。
テーブルAにトリガーなどはありませんか？
テーブルAにはログ項目（更新日時や更新機能など）があれば確認確認の手助けになりますけど。
補足情報に、更新前後のデータ画面を追加しました。ご確認いただければ幸いです。
テーブルAに更新日時がありました。実際にはテーブルBの年月と日付を結合したクエリ(Q)とテーブルAでデータ更新を行っています。
ここから何か分かれば幸いです。
lastupdate項目も更新しないと駄目な仕様に見えますし、更新すればその更新クエリーで更新したかどうかがはっきりしますね。
質問に追加された内容では、どう見ても全件更新されたようには見えません。
lastupdateの項目に関しては、MySQLのデータ型をTimestamp型にしているので、更新があれば自動的に更新されるものと考えています。
初期値というのはありますけど、項目を指定せずに勝手に更新されるなんてことはありませんよ。
直接値を更新した場合に、項目指定せずに更新されましたか？
勉強不足なもので、まだよく分かっていないのですが、MySQLのlastupdate(timestamp型)の属性にon updateを指定しています。
なので、テーブルから直接レコード内データを編集したり、SQL文でデータ更新されると、lastupdate項目は自動的に更新されていました。
提示していただいたSET文(日付を指定する箇所)についてですが、次のSQLにて実行しましたが、やはり1,4,5,8日が空白になりました。
UPDATE A INNER JOIN B ON (A.社員No = B.社員Ｎｏ) AND (A.年月日 = B.年月日) SET A.勤務 = B.勤務, A.出勤 = B.年月日 & ' ' & B.出勤, A.退勤 = B.退勤
WHERE B.社員Ｎｏ=18895;
※Bのテーブルはクエリで年月日に置き換えています。
on updateが付いてるなら更新されますね。（質問に無い内容は分かりません）
質問に追記された内容では、lastupdateの内容が更新されていないものがありませんでしたか？
更新されているなら、lastupdateの時系列が変です。
何か他の処理があるんじゃないでしょうか。
同じ構造のテーブルを別名で用意して、そのテーブルに対しての更新を掛けてみるとか。
お世話になります。
同じ構造のテーブルを用意、更新をかけましたが結果としては同様になりました。lastupdateの時刻も同様で、特定レコードだけ更新されていました。
SQL的にはデータが変わらずともlastupdateは更新されると踏んでいるのですが…。
追記2のSQLで実行しましたが、こちらもまったく同じ結果となりました。
原因が不明な状況なので、取りあえず動作するという事であれば、現状の直接値を設定した場合は上手く動作するという事を担保に、テーブルBを１件ずつ読み込んで、１件毎に値でUPDATEするという方法が考えられます。
件数が多いようだと、処理時間が掛かってしまうというのはありますけど。
若しくは、テーブルBをMySQL側に用意して、そこにINSERT。
そして、パススルークエリーでMYSQL側のテーブル同士で更新する。
というようにすれば、１件毎ではないので、そこまで遅くはないと思います。
件数が20000件を超えるので、1件ずつのUPDATEは避けたいと考えています。
そしてご報告になりますが、テーブルBは別ファイルのMDBからリンクテーブルとして参照しておりました。この参照ファイルを本環境から再度コピーし直したところ、正常に更新が行われることが確認できました。
テスト環境を準備している際に元データに何らかの異常が生じてしまったのではないかと考えております。(見た目のデータには何ら異常ないのが腑に落ちませんが)
sazi様にはここまで様々な助言をいただき感謝の気持ちでいっぱいです。
私の環境で用意した元データの不具合という結論に至り、申し訳ない気持ちもありますが、ここまでご協力いただき、本当にありがとうございました。
ACCESSのテーブルのデザイン変更すると、おかしな動作になる場合があります。
最適化/修復でも改善しないので、
・テーブルをエクスポート(XML)→インポート(XML)
を行う事をお薦めします。
※MSのサポート情報があったはずですが、失念。
適用後にデータサイズも圧縮される場合が殆どです。
確かにデザイン変更を弄った記憶があります。
以後気を付けたいと思います。
重ね重ね、ありがとうございました。
0
ODBCの日付時刻形式を使うのではないでしょうか。
https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2005/ms190234(v=sql.90)
投稿2019/04/08 07:41
総合スコア12
ご回答ありがとうございます。
提示されたURLおよび検索して調べてみたのですが、実際のところ、クエリにどう組み込んだらよいのかがよく分かりませんでしたので、引き続き調べてみたいと思います。
リンク先はSQL－SERVERの話では？
あなたの回答
tips
プレビュー
