マルチテーブル関連付け複数の異なるレコードメソッドの共有を同時に更新
6396 ワード
以下にテスト例を示します.
1.最初に2つのテンポラリ・テーブルを作成し、テスト・データを入力します.
このときのテーブルデータは次のようにクエリされます.
#temptest1 #temptest2
2.現在、#temptest 2の年齢を対応する#temptest 1の年齢に更新します.
実は[表1]のIDが1の年齢を19とし,IDが2の年齢を20とした.
もちろんここの要求はSQL 1つだけで、ループは使えません.
結果は次のとおりです.
実装方法は次のとおりです.
Update t1
Set t1 .age = t2.age
From #temptest1 t1
Join #temptest2 t2
On t1.id = t2.id
(補足)Sql Server 2008 Mergeコマンド書き方:
merge into #temptest1 t1 using(select age,id from #temptest2) t2on t1.id = t2.idwhen matched thenupdate set t1.age = t2.age
面白いSqlではないでしょうか.複数の異なる値のレコードヘッダーを一度に更新する方法については、次の2つのテーブルがあると仮定します.
既存レコード
testA:
ID ENG CHI
===============
1 a 1
2 b 2
3 c 3
testB:
ID ENG CHI ANY....
=================
1 d 4
2 e 5
3 f 6
testBのレコードのENG,CHIフィールドをtestAに更新してIDで対応したいと思います.
CODE:
SQL> set autot on
SQL> update ta set ta.b=(select tb.b from tb where ta.a=tb.a) where exists (select 1 from tb where ta.a=tb.a);
4行が更新されました.
経過時間:00:00:00.01
計画の実行
----------------------------------------------------------
Plan hash value: 1137212925
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 5 | 165 | 20 (30)| 00:00:01 |
| 1 | UPDATE | TA | | | | |
|* 2 | HASH JOIN SEMI | | 5 | 165 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TA | 5 | 100 | 2 (0)| 00:00:01 |
| 4 | VIEW | VW_SQ_1 | 4 | 52 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| TB | 4 | 52 | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | TB | 1 | 26 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TA"."A"="ITEM_1")
6 - filter("TB"."A"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
統計
----------------------------------------------------------
0 recursive calls
4 db block gets
23 consistent gets
0 physical reads
1004 redo size
840 bytes sent via SQL*Net to client
856 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
SQL> update ta set ta.b=(select tb.b from tb where ta.a=tb.a) where ta.a= (select tb.a from tb where ta.a=tb.a);
4行が更新されました.
経過時間:00:00:00:00.00
計画の実行
----------------------------------------------------------
Plan hash value: 3571861550
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 20 | 7 (15)| 00:00:01 |
| 1 | UPDATE | TA | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL| TA | 5 | 100 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TB | 1 | 13 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | TB | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TA"."A"= (SELECT "TB"."A"FROM "TB""TB"WHERE
"TB"."A"=:B1))
4 - filter("TB"."A"=:B1)
5 - filter("TB"."A"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
統計
----------------------------------------------------------
11 recursive calls
1 db block gets
53 consistent gets
0 physical reads
588 redo size
840 bytes sent via SQL*Net to client
858 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
create unique index tb_a_uidx on tb(a);
[Copy to clipboard] [ - ]
CODE:
SQL> update (select ta.b tab1 ,tb.b tbb from ta,tb where ta.a=tb.a) set tab1=tbb;
4行が更新されました.
経過時間:00:00:00.01
計画の実行
----------------------------------------------------------
Plan hash value: 1761655026
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 4 | 184 | 5 (20)| 00:00:01 |
| 1 | UPDATE | TA | | | | |
|* 2 | HASH JOIN | | 4 | 184 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TB | 4 | 104 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TA | 5 | 100 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TA"."A"="TB"."A")
Note
-----
- dynamic sampling used for this statement (level=2)
統計
----------------------------------------------------------
8 recursive calls
4 db block gets
17 consistent gets
0 physical reads
1004 redo size
840 bytes sent via SQL*Net to client
827 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processed
1.最初に2つのテンポラリ・テーブルを作成し、テスト・データを入力します.
create table #temptest1
(
id int,
name1 varchar(50),
age int
)
create table #temptest2
(
id int,
name1 varchar(50),
age int
)
このときのテーブルデータは次のようにクエリされます.
#temptest1 #temptest2
2.現在、#temptest 2の年齢を対応する#temptest 1の年齢に更新します.
実は[表1]のIDが1の年齢を19とし,IDが2の年齢を20とした.
もちろんここの要求はSQL 1つだけで、ループは使えません.
結果は次のとおりです.
実装方法は次のとおりです.
Update t1
Set t1 .age = t2.age
From #temptest1 t1
Join #temptest2 t2
On t1.id = t2.id
(補足)Sql Server 2008 Mergeコマンド書き方:
merge into #temptest1 t1 using(select age,id from #temptest2) t2on t1.id = t2.idwhen matched thenupdate set t1.age = t2.age
面白いSqlではないでしょうか.複数の異なる値のレコードヘッダーを一度に更新する方法については、次の2つのテーブルがあると仮定します.
create table testA(
id number,
eng varchar2(3),
chi varchar2(3)
)
create table testB(
id number,
eng varchar2(3),
chi varchar2(3)，
anythingother varchar2(1)
)
既存レコード
testA:
ID ENG CHI
===============
1 a 1
2 b 2
3 c 3
testB:
ID ENG CHI ANY....
=================
1 d 4
2 e 5
3 f 6
testBのレコードのENG,CHIフィールドをtestAに更新してIDで対応したいと思います.
CODE:
SQL> set autot on
SQL> update ta set ta.b=(select tb.b from tb where ta.a=tb.a) where exists (select 1 from tb where ta.a=tb.a);
4行が更新されました.
経過時間:00:00:00.01
計画の実行
----------------------------------------------------------
Plan hash value: 1137212925
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 5 | 165 | 20 (30)| 00:00:01 |
| 1 | UPDATE | TA | | | | |
|* 2 | HASH JOIN SEMI | | 5 | 165 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TA | 5 | 100 | 2 (0)| 00:00:01 |
| 4 | VIEW | VW_SQ_1 | 4 | 52 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| TB | 4 | 52 | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | TB | 1 | 26 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TA"."A"="ITEM_1")
6 - filter("TB"."A"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
統計
----------------------------------------------------------
0 recursive calls
4 db block gets
23 consistent gets
0 physical reads
1004 redo size
840 bytes sent via SQL*Net to client
856 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
SQL> update ta set ta.b=(select tb.b from tb where ta.a=tb.a) where ta.a= (select tb.a from tb where ta.a=tb.a);
4行が更新されました.
経過時間:00:00:00:00.00
計画の実行
----------------------------------------------------------
Plan hash value: 3571861550
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | 20 | 7 (15)| 00:00:01 |
| 1 | UPDATE | TA | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL| TA | 5 | 100 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TB | 1 | 13 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | TB | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TA"."A"= (SELECT "TB"."A"FROM "TB""TB"WHERE
"TB"."A"=:B1))
4 - filter("TB"."A"=:B1)
5 - filter("TB"."A"=:B1)
Note
-----
- dynamic sampling used for this statement (level=2)
統計
----------------------------------------------------------
11 recursive calls
1 db block gets
53 consistent gets
0 physical reads
588 redo size
840 bytes sent via SQL*Net to client
858 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
create unique index tb_a_uidx on tb(a);
[Copy to clipboard] [ - ]
CODE:
SQL> update (select ta.b tab1 ,tb.b tbb from ta,tb where ta.a=tb.a) set tab1=tbb;
4行が更新されました.
経過時間:00:00:00.01
計画の実行
----------------------------------------------------------
Plan hash value: 1761655026
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 4 | 184 | 5 (20)| 00:00:01 |
| 1 | UPDATE | TA | | | | |
|* 2 | HASH JOIN | | 4 | 184 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TB | 4 | 104 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TA | 5 | 100 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TA"."A"="TB"."A")
Note
-----
- dynamic sampling used for this statement (level=2)
統計
----------------------------------------------------------
8 recursive calls
4 db block gets
17 consistent gets
0 physical reads
1004 redo size
840 bytes sent via SQL*Net to client
827 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
4 rows processed