【SQL/Update】更新データの抽出条件に「SELECT結果のある値がすべてxxなら」という表現を組み込みたいです。

Question

AテーブルはAのID（以下AID）、Aのステータス（以下Aステータス）のカラムを持ちます。
BテーブルはBのID（以下BID）、AID、Bのステータス（以下Bステータス）のカラムを持ちます。
AとBは1:Nの関係にあります（ヘッダと明細の関係）。
BのAIDはAのAIDを参照しています。
RDBMSはFirebirdです。

このとき、
A.AID = B.AID かつ B.Bステータスがすべて「完了」であれば、
A.Aステータスを「完了」に更新するというSQLを組みたいです。

下記パターン1・2まではSQLを作成したのですが、
"B.Bステータスがすべて「完了」であれば"という条件をどう表現すれば良いか分かりません。
どのようなSQLにすれば良いか教えてください。
B.Bステータスの値は「完了」以外にもいくつかあるため、すべて「完了」になった時点で
親テーブルであるAのAステータスを「完了」としたいです。

■パターン1

Update A 
Set A.ステータス = '完了'
From B
Where A.AID = B.AID

■パターン2

Update A 
Set A.ステータス = '完了'
WHERE EXISTS
(
Select 1 From B
Where A.AID = B.AID
)

宜しくお願い致します。

Comments

[Orlofsky]

SQLを含むコードは https://teratail.com/help#about-markdown の[コードを入力]に修正してください。

[Orlofsky]

質問にCREATE TABLE文と数件で良いからテーブルに存在するデータをINSERT文で提示すると適切なコメントが付き易いです。

[sazi]

Bステータスの取りうる値は限定されていますか？
その場合はどのような値でしょう？

Answer 1

mysql的な書き方だとこんなふうにいけます

• 元データ

create table tbl_a (aid int unique,status varchar(10) default '');
insert into tbl_a values(1,''),(2,''),(3,'');
create table tbl_b (bid int unique,aid int,status varchar(10) default '');
insert into tbl_b values(1,1,''),(2,1,''),(3,1,''),(4,2,'完了'),(5,2,'完了'),(6,2,'完了'),(7,3,'完了'),(8,3,''),(9,3,'');

• 更新

insert into tbl_a(aid) 
select aid from tbl_b 
group by aid
having sum(status='完了')=count(aid)
on duplicate key update status='完了'

Comments

[sazi]

mysqlだとupdateよりも回答の内容の方がメリットあるんでしょうか？

[sazi]

因みにFireBirdでは、on duplicate key updateは使えないと思います。
https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-insert.html

[yambejp]

基本的にリレーションさせるよりバルクで流し込むほうが
構造も単純だし高速化しやすいという意図です。
もちろんFireBirdがなんらかのバルク処理ができるなら
それに合わせて書き換える必要はあるでしょうね
できないならリレーションしてもいいでしょう

[sazi]

status!='完了`の条件だと件数次第では正のが出せない場合もありそうですよね。
statusのパターンが固定なら問題ないですけど、そうでない場合は、yambejpさんの回答のような条件の取り方の方が高速かもしれません。

[tea-leaf]

saziさん、yambejpさん
質問が2回目で慣れていないこともあり、諸々稚拙な内容であったかと思いますが、ご意見くださり有難うございました。
Firebirdで使用不可の関数やmysqlのケースなど、知らなかったことを勉強できました。

Answer 2

この手のSQLではEXISTS句を使うケースが多いですが、
ちょっと感覚的に難しいと思うので簡単な方法で。

副問い合わせで完了以外をカウントしたときに０だったらいいので、
以下のようになります。

UPDATE A 
SET A.STATUS = '完了'
WHERE A.AID = 
(
  SELECT AID FROM 
    ( SELECT B.AID, COUNT(B.BID) AS count FROM B WHERE B.STATUS != '完了' GROUP BY AID )
  WHERE count = 0
)

EXISTS句を使う場合はこのようになります。

UPDATE A 
SET A.STATUS = '完了'
WHERE NOT EXISTS 
(
  SELECT 1 FROM B WHERE B.STATUS != '完了' AND B.AID = A.AID
)

Comments

[tea-leaf]

mackerel6.023さん
教えて頂いたクエリで解決しました。
大変助かりました。
"不要なレコードのSELECT結果が存在しなければ"という抽出方法、いろいろなところで応用できそうで勉強になりました。
有難うございます！