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 回答日時： 2009/09/30 21:06

#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)してたりするということで、あらためて難しさを確認できました。

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

お礼日時：2009/10/01 08:37

No.3 回答者： jamshid6 回答日時： 2009/09/30 15:49

#1です。

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

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

この回答へのお礼

ありがとうございます。

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

お礼日時：2009/09/30 18:25

No.2 回答者： nda23 回答日時： 2009/09/30 09:44

DBが何か分からないのですが、一般にテーブルを結合した場合、

１：１か１：ｎの１側しか更新できません。よって、Test1のt1が
一意である保証がないと更新できません。

この回答へのお礼

ありがとうございます。

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

DBは、sqlserverとなります。

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

お礼日時：2009/09/30 12:14

No.1 回答者： jamshid6 回答日時： 2009/09/30 01:00

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

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

この回答へのお礼

ありがとうございます。

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

とても助かりました。

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

お礼日時：2009/09/30 12:15