質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

2回答

5764閲覧

AccessからMySQLのデータベース更新について

ysk1118

総合スコア22

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2019/04/08 01:42

編集2019/04/08 08:24

前提・実現したいこと

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 画像削除させていただきました。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sazi

2019/04/08 07:44

テーブルAは、 年月日:日付/時刻型、出勤時刻:時刻型、退勤時刻:時刻型ではなく、 年月日:日付型、出勤時刻:日付/時刻型、退勤時刻:日付/時刻型じゃないですか?
ysk1118

2019/04/08 07:48

おっしゃる通りです。Accessでリンクテーブルのデザインで開くと日付/時刻型ですが、MySQL(phpMyAdmin)では、Time型となっています。
sazi

2019/04/08 08:05

直接値を指定して更新されOKだったクエリーも追記して貰えますか。
ysk1118

2019/04/08 08:13

UPDATE A INNER JOIN B ON (A.社員No = B.社員No) AND (A.年月日 = B.年月日) SET A.勤務 = B.勤務, A.出勤 = '11:00', A.退勤 = B.退勤 WHERE B.社員No=18895; こちらで実行し、出勤時刻が全日において2019/04/08 11:00となりました。
guest

回答2

0

ベストアンサー

テキスト型である出勤時刻および退勤時刻はどのような書式で格納されていますか?
更新先が時刻型であるので、テキスト型でHHMMなど格納されているのであれば、HH:MMのように変換しないと駄目じゃないでしょうか。
但し、ODBCドライバーが暗黙変換してくれればの話ですので、駄目な場合は型を合わせる必要があると思います。

追記

SQL

1UPDATE A INNER JOIN B ON (A.社員No = B.社員No) AND (A.年月日 = B.年月日) 2SET A.勤務 = B.勤務, A.出勤 = '11:00', A.退勤 = B.退勤

これで問題ないという事なので、更新されているのかどうかをlastupdateを更新するようにしてみると更新されているかどうかが分かると思います。

SQL

1UPDATE A INNER JOIN B ON (A.社員No = B.社員No) AND (A.年月日 = B.年月日) 2SET A.勤務 = B.勤務, A.出勤 = B.出勤, A.退勤 = B.退勤, A.lastupdate=Now()

追記2

暗黙変換のせいかもしれませんので、以下を試してみてください

SQL

1UPDATE A INNER JOIN B ON (A.社員No = B.社員No) AND (A.年月日 = B.年月日) 2SET A.勤務 = B.勤務, A.出勤 = CStr(B.出勤), A.退勤 = CStr(B.退勤)

投稿2019/04/08 02:51

編集2019/04/08 08:31
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ysk1118

2019/04/08 04:20

ご回答ありがとうございます。 Bテーブルの出勤時刻(テキスト型)は、定型入力=##:##としています。 変換を明示するべく、ACCESS側のSQLでCDate()でキャストするようにしてみましたが、うまくいきませんでした。
sazi

2019/04/08 04:30

定型入力は格納されるデータそのものではありませんよ。 データの内容を確認して下さい。
sazi

2019/04/08 04:35 編集

また、データが格納されないのは型変換のエラーになっているはずですので、update文をクエリーで実行してみれば、詳細が分かると思います。
ysk1118

2019/04/08 05:12

お恥ずかしながら、データそのものの確認はどうしたらできるのでしょうか。 私の認識としては、定型入力##:##としているので、データも例えば 12:34というテキストデータが格納されているものと考えていたのですが…。 Update文を更新クエリとして実行してみたところ、特にエラーなどは表示されませんでした。
sazi

2019/04/08 05:48

テーブルを開いて12:34というテキストデータが格納されているのを確認されていますか? また、更新クエリーでテーブルBを指定せず、直接値で更新するとどうなるでしょうか。
ysk1118

2019/04/08 06:39 編集

テーブルを開いたところ、12:34というデータでした。 直接値での更新('12:34'を指定)では更新できました。 また、不可思議なことにAテーブルの1ヶ月分のデータ(30件)のうち、1日と4日5日の出勤時刻がUpdate文実行後に消えてしまいました。もちろん、更新元データであるBの方にはちゃんとデータがあります。 元々、本稼働しているシステム環境からテスト環境を作って機能改善を図っている途中でこの現象に遭遇しています。 テスト環境を作る過程で何か問題が生じているのかもしれません。
sazi

2019/04/08 07:00

事象からすると、別な処理で更新しているように見えますね。 テーブルAにトリガーなどはありませんか? テーブルAにはログ項目(更新日時や更新機能など)があれば確認確認の手助けになりますけど。
ysk1118

2019/04/08 07:39

補足情報に、更新前後のデータ画面を追加しました。ご確認いただければ幸いです。 テーブルAに更新日時がありました。実際にはテーブルBの年月と日付を結合したクエリ(Q)とテーブルAでデータ更新を行っています。 ここから何か分かれば幸いです。
sazi

2019/04/08 07:57 編集

lastupdate項目も更新しないと駄目な仕様に見えますし、更新すればその更新クエリーで更新したかどうかがはっきりしますね。 質問に追加された内容では、どう見ても全件更新されたようには見えません。
ysk1118

2019/04/08 08:05

lastupdateの項目に関しては、MySQLのデータ型をTimestamp型にしているので、更新があれば自動的に更新されるものと考えています。
sazi

2019/04/08 08:07

初期値というのはありますけど、項目を指定せずに勝手に更新されるなんてことはありませんよ。 直接値を更新した場合に、項目指定せずに更新されましたか?
ysk1118

2019/04/08 08:22

勉強不足なもので、まだよく分かっていないのですが、MySQLのlastupdate(timestamp型)の属性にon updateを指定しています。 なので、テーブルから直接レコード内データを編集したり、SQL文でデータ更新されると、lastupdate項目は自動的に更新されていました。 提示していただいたSET文(日付を指定する箇所)についてですが、次のSQLにて実行しましたが、やはり1,4,5,8日が空白になりました。 UPDATE A INNER JOIN B ON (A.社員No = B.社員No) AND (A.年月日 = B.年月日) SET A.勤務 = B.勤務, A.出勤 = B.年月日 & ' ' & B.出勤, A.退勤 = B.退勤 WHERE B.社員No=18895;  ※Bのテーブルはクエリで年月日に置き換えています。
sazi

2019/04/08 08:36 編集

on updateが付いてるなら更新されますね。(質問に無い内容は分かりません) 質問に追記された内容では、lastupdateの内容が更新されていないものがありませんでしたか? 更新されているなら、lastupdateの時系列が変です。 何か他の処理があるんじゃないでしょうか。
sazi

2019/04/08 08:38

同じ構造のテーブルを別名で用意して、そのテーブルに対しての更新を掛けてみるとか。
ysk1118

2019/04/08 23:57

お世話になります。 同じ構造のテーブルを用意、更新をかけましたが結果としては同様になりました。lastupdateの時刻も同様で、特定レコードだけ更新されていました。 SQL的にはデータが変わらずともlastupdateは更新されると踏んでいるのですが…。 追記2のSQLで実行しましたが、こちらもまったく同じ結果となりました。
sazi

2019/04/09 01:10

原因が不明な状況なので、取りあえず動作するという事であれば、現状の直接値を設定した場合は上手く動作するという事を担保に、テーブルBを1件ずつ読み込んで、1件毎に値でUPDATEするという方法が考えられます。 件数が多いようだと、処理時間が掛かってしまうというのはありますけど。
sazi

2019/04/09 01:13

若しくは、テーブルBをMySQL側に用意して、そこにINSERT。 そして、パススルークエリーでMYSQL側のテーブル同士で更新する。 というようにすれば、1件毎ではないので、そこまで遅くはないと思います。
ysk1118

2019/04/09 01:21

件数が20000件を超えるので、1件ずつのUPDATEは避けたいと考えています。 そしてご報告になりますが、テーブルBは別ファイルのMDBからリンクテーブルとして参照しておりました。この参照ファイルを本環境から再度コピーし直したところ、正常に更新が行われることが確認できました。 テスト環境を準備している際に元データに何らかの異常が生じてしまったのではないかと考えております。(見た目のデータには何ら異常ないのが腑に落ちませんが) sazi様にはここまで様々な助言をいただき感謝の気持ちでいっぱいです。 私の環境で用意した元データの不具合という結論に至り、申し訳ない気持ちもありますが、ここまでご協力いただき、本当にありがとうございました。
sazi

2019/04/09 01:51 編集

ACCESSのテーブルのデザイン変更すると、おかしな動作になる場合があります。 最適化/修復でも改善しないので、 ・テーブルをエクスポート(XML)→インポート(XML) を行う事をお薦めします。 ※MSのサポート情報があったはずですが、失念。 適用後にデータサイズも圧縮される場合が殆どです。
ysk1118

2019/04/09 02:44

確かにデザイン変更を弄った記憶があります。 以後気を付けたいと思います。 重ね重ね、ありがとうございました。
guest

0

ODBCの日付時刻形式を使うのではないでしょうか。

https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2005/ms190234(v=sql.90)

投稿2019/04/08 07:41

takugin

総合スコア12

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ysk1118

2019/04/08 07:49

ご回答ありがとうございます。 提示されたURLおよび検索して調べてみたのですが、実際のところ、クエリにどう組み込んだらよいのかがよく分かりませんでしたので、引き続き調べてみたいと思います。
sazi

2019/04/08 07:52

リンク先はSQL-SERVERの話では?
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問