AccessVBA・SQLでUPDATEステートメントでのサブクエリ代用方法

Question

前提・実現したいこと

AccessVBA、SQLを使用しています。
条件にあったレコードの対象フィールドにフラグ（"選択済"）を入力して、条件を変えてまたフラグを入力して、という連続した処理を行いたいのですが、AccessVBAでSQLを書くと、UPDATEステートメントではサブクエリを使えないらしい、ということが調べてみて分かりました。

下記のようなコードをイメージしていました。
また、後続のコードではこのコードのサブクエリWHERE句の条件を変えたり、フラグ入力するフィールドを変えたりしたものをいくつも書いて、連続した処理としたいです。

UPDATEで更新できない場合、中間テーブルを用意してそっちに一度フラグ内容を入力して、そこから結合させる、といった回りくどい方法しか思いつかなかったのですが、もっと効率的な良い方法はないでしょうか。
できれば一つのSQL文だけでテーブルの更新まで行いたいと思っているのですが、調べてもわからず行き詰まっていました。
もし、ヒントや方向性、参考になるような事例だけでもあればご教示いただけるとありがたいです。

※初心者なので、そもそもの認識や知識が間違っているようでしたらそれもご指摘いただけると大変助かります。

どうぞよろしくお願いいたします。

Dim db As DAO.Database
Set db = CurrentDb

Dim mySql As String

mySql = "UPDATE Tab1"
mySql = mySql & " SET col1 = "選択済""
mySql = mySql & " WHERE EXISTS" 
mySql = mySql & " (SELECT TOP 30 * from Tab1" 
mySql = mySql & " WHERE col2 = 1" 
mySql = mySql & " ORDER BY Rnd([主キー]))"

db.Execute mySql, dbFailOnError

------
以下の通りサブクエリ内の条件を一部変えて、同様の処理をいくつも続ける
(SELECT TOP 30 * from Tab1 
WHERE col2 = 2
ORDER BY Rnd([主キー]))

------
・
・
・

補足情報（FW/ツールのバージョンなど）

Access2016

Comments

[sazi]

> UPDATEステートメントではサブクエリを使えないらしい、ということが調べてみて分かりました。
一体どこの情報ですか？

[sousuke]

exists句にtop30のorderby rndって何か意味ありますか？existsは基本的にtop1と同等なんであんまり意味ないと思います。

[tatsutatsu]

sazi様
https://teratail.com/questions/122998
https://asai-atsushi.blog.ss-blog.jp/2018-09-04
WHEREで使えないとは書かれていないので、読み違えているのかもしれません。

sousuke様
https://uen1130.hatenadiary.org/entry/20150729/p1
上記参考にして、条件に合致したレコードからランダムに30件抽出したく、このようなコードとしました。こちらもおかしいようですね。。ご指摘ありがとうございます。
もし、代替のコードあればご教示いただければ幸いです。

Answer 1

UPDATE Tab1 SET col1 = "選択済"
where ～

上記のように更新対象が一つのテーブルであれば、where 条件でどのようなサブクエリを使用しても、更新できないクエリーにはなりません。

更新できないクエリーになるのは、主に更新対象を特定できない時です。

サブクエリ内の条件を一部変えて、同様の処理をいくつも続ける

同様な内容であれば問題ないと思います。

追記

UPDATE Tab1 SET col1 = "選択済"
where 主キー in (
         SELECT TOP 30 主キー from Tab1 
         WHERE col2 = 2
         ORDER BY Rnd(主キー)
     )

Comments

[tatsutatsu]

ご回答ありがとうございます。
再度同内容のコードで試してみたのですが、テーブルの全レコードに"選択済"が入力され、WHERE EXISTS〜以降が無視された状態でクエリが実行されているような結果になりました。

何か考えられる原因はあるでしょうか？

[sazi]

> 何か考えられる原因はあるでしょうか？
意図通りの条件になっていないとしか言えません。

質問に書かれているwhere条件の意図が良く分かりませんが、ランダムな主キーに対しての更新という事で追記しました。

[sazi]

質問へのコメントを見てやりたいことが理解出来ましたので、SQLを訂正しておきます。
existsは有無を判断するのですから、目的には合っていません。
主キーを限定するのですから　in　を使用します。

[tatsutatsu]

何度もありがとうございます。
ご提示いただいたSQLでやりたいことが実現しました。

＞existsは有無を判断するのですから、目的には合っていません。
　主キーを限定するのですから　in　を使用します。

こちらの基本理解と、UPDATEでのサブクエリ利用の理解がどちらも低かったためうまくできなかったようです。
もう少し基礎理解をしつつ、SQL実装したいと思います。
とても良い気付きとなりました。ありがとうございます。

[sazi]

existsでも相関でキー参照は可能ですけど、相関ではrnd()で都度複数件する事になりますので、やはり目的には合いません。

[tatsutatsu]

追加でありがとうございます。こちらで理解できました。
何度も本当にありがとうございます！
また、是非よろしくお願いします。

Answer 2

UPDATE Tab1
SET col1 = "選択済"
WHERE [主キー] In
 (SELECT TOP 30 [主キー] from Tab1
 WHERE col2 = 1
 ORDER BY Rnd([主キー]))

でいかがでしょうか。

Comments

[tatsutatsu]

回答ありがとうございます。
こちらのSQLで実現しました。

また、機会があればよろしくお願いします。

[kitasue]

回答の編集時刻をご確認くださいね。

[tatsutatsu]

失礼しました。以後確認するようにいたします。

Answer 3

UPDATEステートメントではサブクエリを使えないらしい

これは条件によって使えない場合があるという事で今回のWhereに対してのサブクエリなら大丈夫ですよ
参照系のクエリをサブクエリ付で作成してみて表示された値が変更できるかで判断がつくと思います。

ただ今後更新できないクエリの場合なら主キーだけ配列に残して後からまとめて更新という手段もあります。
(ワークテーブルを使いたくない場合)
その場合BeginTrans、CommitTransがキーポイントとなります。

ご参考までに。

追記
【SQL】5分でわかる！ EXISTSでサブクエリを扱う方法のように
WHERE EXISTSで親子関係があるならその親子条件を記載しなければなりません。
(この場合親の[主キー]と子の[主キー])

Comments

[tatsutatsu]

ご回答ありがとうございます。
先のご回答者様と同様の内容となり恐縮ですが、よろしければご確認お願いいたします。
---------
再度同内容のコードで試してみたのですが、テーブルの全レコードに"選択済"が入力され、WHERE EXISTS〜以降が無視された状態でクエリが実行されているような結果になりました。

何か考えられる原因はあるでしょうか？

[tatsutatsu]

追記でいただいた親子関係についてですが、今回のコードでは1テーブルしか使用していません。追記いただい内容は複数テーブルを使用している場合、との理解であっているでしょうか？

[kuma_kuma_]

いいえ。同じテーブルでも条件は必要ですよ。
追加クエリとしてではなく参照クエリで一度確認すると判ると思います。
今の状態ですと絞り込みができず全件表示されるはずです。

Select [主キー], col1
From Tab1
WHERE EXISTS
(SELECT TOP 30 * from Tab1
WHERE col2 = 1
ORDER BY Rnd([主キー]))

[tatsutatsu]

ありがとうございます。確認してみたところ、ご指摘の通り絞り込みできておらず全件表示されていました。もう少し検討してみます。

[kuma_kuma_]

Select [主キー], col1
From Tab1 AS Tab1_A
WHERE EXISTS
(SELECT TOP 30 * from Tab1 AS Tab1_B
WHERE Tab1_A.[主キー] = Tab1_B.[主キー]
AND col2 = 1
ORDER BY Rnd([主キー]))

[tatsutatsu]

改めていただいたSQL試してみましたが、構文エラーとなりうまくいきませんでした。
同じテーブルでも条件には必要とのことでAとBに分けて書かれているのかと思いますが、先に提示いただいたURLでの事例では同一テーブルでの条件書きがなかったので、これらを整理できるまでまだ私の理解が追いついていないようです。もう少し深掘って考えてみます。
何度も返答いただいたのにすべてを理解できず申し訳ありません。
ありがとうございます。