UPDATEについて(結合による)

質問

次のような操作がしたいのですが・・・
Test1
t1 t2
1 A
2 B

Test2
tt1 tt2 tt3
1 1 AA
1 2 BB
2 1 CC

結果
Test1とTest2を関係付けて(Test.t1=Test2.tt1のinnerjoin)
Test2.tt3が少なくともAAである場合に
Test1.t2をDにしたいのです。

アドバイスあれば、宜しくお願いします。

回答No.4

jamshid6 · ベストアンサー

#1,#3です。
>内部的には、一度テーブル結合し、Test2.tt3='AA'を探しだしているということですかね。
>その上で更新しているのでしょうか。

考え方としては近いです。
ただし、実際に確認してみると、SQL Serverのオプティマイザは以下の通りもっと性能がよく、「各レコードの更新回数がｎ回になるだけ」というのは正しくなかったので訂正させていただきます。

----------------------------------------------------------------------------------------------------------------
データを少し変えて重複させ、どういうプランで実行するか見てみます。
Test1
t1 t2
1 A　　--t1=1を２件いれます
1 A　　--t1=1を２件いれます
2 B

Test2
tt1 tt2 tt3 tt4
1 1 AA X　　--t1=1を２件いれます(ただしtt4には別の値を)
1 1 AA Y　　--t1=1を２件いれます(ただしtt4には別の値を)
1 2 BB Z
2 1 CC W

SET STATISTICS PROFILE ON
GO

UPDATE Test1
SET t2=b.tt4 --敢えてTest2のtt4で更新させてみます。
FROM Test1 a
INNER JOIN Test2 b ON b.tt1=a.t1 AND b.tt3='AA'
GO

Rows　Executes　StmtText
2　　　1　　　　UPDATE Test1 SET t2=b.tt4 FROM Test1 a INNER JOIN Test2 b ON b.tt1=a.t1 AND b.tt3='AA'
2　　　1　　　　　|--Table Update(OBJECT:([Test1]), SET:([Test1].[t2] = [Expr1006]))
2　　　1　　　　　　|--Top(ROWCOUNT est 0)
0　　　0　　　　　　　|--Compute Scalar(DEFINE:([Expr1006]=CONVERT_IMPLICIT(varchar(2),[Test2].[tt4] as [b].[tt4],0)))
2　　　1　　　　　　　　|--Stream Aggregate(GROUP BY:([Bmk1000]) DEFINE:([b].[tt4]=ANY([Test2].[tt4] as [b].[tt4])))
4　　　1　　　　　　　　　|--Nested Loops(Inner Join, WHERE:([Test2].[tt1] as [b].[tt1]=[Test1].[t1] as [a].[t1]))
3　　　1　　　　　　　　　　|--Table Scan(OBJECT:([Test1] AS [a]))
6　　　3　　　　　　　　　　|--Table Scan(OBJECT:([Test2] AS [b]), WHERE:([Test2].[tt3] as [b].[tt3]='AA'))

SELECT * FROM Test1
GO

t1 t2
1 X
1 X
2 B

----------------------------------------------------------------------------------------------------------------
この通り、
1)「Test1」と「Test2のうちtt3='AA'のもの」を結合して、４行を得る
　（Test1とTest2両方にt1=1のものを２行ずつ入れましたから、２×２に重複されて４行になります。

2)ただ、更新するのはTest1のうちの２行なので、２行を２回ずつ更新するのは無駄。したがって集約して２行にしてしまう
　このときに「ANY([Test2].[tt4] as [b].[tt4])」とあるように、tt4の値が複数あったら任意の１つ(つまりXかYかどちらか）にしてしまいます。

3)最後に２行を任意のtt4(例えばX)で更新する。

データ件数が多くなると、集約方法が「並び替え＋DISTINCT」になったりしますが、基本的な考え方は変わらないようです。

以上、ご参考まで。

お礼

大変丁寧な解説、ありがとうございます。

単純な結合ではなくて、更新行を集約(並び替え+DISTINCT)してたりするということで、あらためて難しさを確認できました。

参考にさせていただきます。_(__)_

回答No.3

jamshid6

#1です。
>一般にテーブルを結合した場合、１：１か１：ｎの１側しか更新できません。

少なくともSQL Serverをお使いであれば、これはあてはまりません。
特に今回の場合、Test2のフィールドの値でTest1のフィールドを更新するわけではなく、'D'という１つの固定値で更新するわけですから、仮にｎ：ｎであっても問題ないです（各レコードの更新回数がｎ回になるだけ）。

お礼

ありがとうございます。

また、ひとつ確認があります。
＞ｎであっても問題ないです（各レコードの更新回数がｎ回になるだけ）。
ということは内部的には、一度テーブル結合し、
Test2.tt3='AA'を探しだしているということですかね。
その上で更新しているのでしょうか。

回答No.2

nda23

DBが何か分からないのですが、一般にテーブルを結合した場合、
１：１か１：ｎの１側しか更新できません。よって、Test1のt1が
一意である保証がないと更新できません。

お礼

ありがとうございます。

そうなんですね、結合されてupdateをかけたことがないので、
とても参考になります。

DBは、sqlserverとなります。

確認ですが、
＞１：１か１：ｎの１側しか更新できません。
とありますが、１側のキー=t1,t2,t3で
n側のキー=t1,t2,t3,t4,t5であれば、
１側は更新できますか？
※複数のキーがある場合でも更新できるのでしょうか？

回答No.1

jamshid6

少なくともAAである場合＝AAを含む場合、という意味ならば

UPDATE Test1
SET t2='D'
FROM Test1 a
INNER JOIN Test2 b ON b.tt1=a.t1 AND b.tt3='AA'

お礼

ありがとうございます。

結合によるupdate処理を行ったことがないので、

とても助かりました。

参考にさせていただきます。