update 子查詢使用介紹，update查詢介紹

update 子查詢使用介紹，update查詢介紹

基礎知識
1， 關聯子查詢和非關聯子查詢

在非關聯子查詢中，內部查詢只執行一次並返回它的值給外部查詢，然後外部查詢在它的處理中使用內部查詢返回給它的值。而在關聯子查詢中，對於外部查詢返回的每一行資料，內部查詢都要執行一次。另外，在關聯子查詢中是資訊流是雙向的。外部查詢的每行資料傳遞一個值給子查詢，然後子查詢為每一行資料執行一次並返回它的記錄。然後，外部查詢根據返回的記錄做出決策。

如：

SELECT o1.CustomerID, o1.OrderID, o1.OrderDateFROM Orders o1WHERE o1.OrderDate = (SELECT Max(OrderDate)FROM Orders o2WHERE o2.CustomerID = o1.CustomerID)

是一個關聯子查詢

SELECT o1.CustomerID, o1.OrderID, o1.OrderDateFROM Orders o1WHERE o1.OrderDate IN(SELECT TOP 2 o2.OrderDateFROM Orders o2WHERE o2.CustomerID = o1.CustomerID)ORDER BY CustomerID

是一個非關聯子查詢

2， 提示（HINT）

一般在最佳化時，無論採用基於規則的或是基於代價的方法，由Oracle系統的最佳化器來決定語句的執行路徑。這樣的選擇的路徑不要見得是最好的。所以，Oracle提供了一種方法叫提示的方法。它可以讓編程人員按照自己的要求來選擇執行路徑，即提示最佳化器該按照什麼樣的執行規則來執行當前的語句。這樣可以在效能上比起Oracle最佳化自主決定要好些。

通常情況下，編程人員可以利用提示來進行最佳化決策。通過運用提示可以對下面內容進行指定：

l SQL語句的最佳化方法；

l 對於某條SQL語句，基於開銷最佳化程式的目標；

l SQL語句訪問的訪問路徑；
l 串連語句的串連次序；
l 串連語句中的串連操作。

如果希望最佳化器按照編程人員的要求執行，則要在語句中給出提示。提示的有效範圍有限制，即有提示的語句塊才能按照提示要求執行。下面語句可以指定提示：

l 簡單的SELECT ,UPDATE ,DELETE 語句；
l 複合的主語句或子查詢語句；
l 組成查詢（UNION）的一部分。

提示的指定有原來的備註陳述式在加“+”構成。文法如下：

[ SELECT | DELETE|UPDATE ] /*+ [hint | text ] */

或

[ SELECT | DELETE|UPDATE ] --+ [hint | text ]

注意在“/*”後不要空就直接加“+”，同樣 “--+”也是連著寫。

警告：如果該提示句書寫不正確，則Oracle就忽略掉該語句。

常見的提示有：

Ordered 強制按照from子句中指定的表的順序進行串連
Use_NL 強制指定兩個表間的串連方式為嵌套迴圈（Nested Loops）
Use_Hash 強制指定兩個表間的串連方式為雜湊串連（Hash Join）
Use_Merge 強制指定兩個表間的串連方式為合并排序串連（Merge Join）
Push_Subq 讓非關聯子查詢提前執行
Index 強制使用某個索引

3， 執行計畫

在PL/SQL Developer的SQL WINDOWS中用滑鼠或鍵盤選中SQL語句，然後按F5，就會出現執行計畫解析的介面：

4， Update的特點

Update的系統內部執行情況可以參照附文：對update事務的內部分析.doc

使用Update的基本要點就是，

1） 盡量使用更新表上的索引，減少不必要的更新
2） 更新的資料來源花費時間儘可能短，如果無法做到就把更新內容插入到中間表中，然後給中間表建上索引，再來更新
3） 如果更新的是主鍵，建議刪除再插入。
5， 樣本用表

後面的闡述將圍繞以下兩張表展開：

Create table tab1 (workdate varchar2(8), cino varchar2(15), val1 number, val2 number);
Create table tab2 (workdate varchar2(8), cino varchar2(15), val1 number, val2 number);
Create table tab3 (workdate varchar2(8), cino varchar2(15), val1 number, val2 number);
Create table tab4 (workdate varchar2(8), cino varchar2(15), val1 number, val2 number);

workdate, cino為兩張表的關鍵字，預設情況沒有建主鍵索引。

二，Update兩種情況

用Update更新某個表，無外乎是兩種情況：根據關聯子查詢，更新欄位；通過非關聯子查詢，限定修改範圍。如果還有第三種情況，那就是前兩種情況的疊加。

1， 根據關聯子查詢，更新欄位

Update tab1 tSet (val1, val2) = (select val1, val2from tab2where workdate = t.workdateand cino = t.cino);

通過tab2來更新tab1的相應欄位。執行SQL語句時，系統會從tab1中一行一行讀記錄，然後再通過關聯子查詢，找到相應的欄位來更新。關聯子查詢能否通過tab1的條件快速的尋找到對應記錄，是最佳化能否實現的必要條件。所以一般都要求在tab2上建有Unique或者排重性較高的Normal索引。執行所用時間大概為（查詢tab1中一條記錄所用的時間 + 在tab2中查詢一條記錄所用的時間）* tab1中的記錄條數。

如果子查詢條件比較複雜，如以下語句：

Update tab1 tSet (val1, val2) = (select val1, val2from tab2 ttwhere exists (select 1from tab3where workdate = tt.workdateand cino = tt.cino)and workdate = t.workdateand cino = t.cino);

這時更新tab1中的每條記錄花費在子查詢上的時間將成倍增加，如果tab1中的記錄數較多，這種更新語句幾乎是不可完成。

解決方式是，把子查詢提取出來，做到中間表中，然後給中間表建上索引，用中間表來代替子查詢，這樣速度就能大大提高：

Insert into tab4select workdate, cino, val1, val2from tab2 ttwhere exists (select 1from tab3where workdate = tt.workdateand cino = tt.cino);create index tab4_ind01 on tab4(workdate, cino);Update tab1 tSet (val1, val2) = (select val1, val2from tab4 ttwhere workdate = t.workdateand cino = t.cino);

2， 通過非關聯子查詢，限定修改範圍

Update tab1 tset val1 = 1where (workdate, cino) in (select workdate, cino from tab2)

根據tab2提供的資料範圍，來更新tab1中的相應記錄的val1欄位。

在這種情況下，系統預設執行方式往往是先執行select workdate, cino from tab2子查詢，在系統中形成系統檢視表，然後在tab1中選取一條記錄，查詢系統檢視表中是否存在相應的workdate, cino組合，如果存在，則更新tab1，如果不存在，則選取下一條記錄。這種方式的查詢時間大致等於：子查詢查詢時間 + （在tab1中選取一條記錄的時間 + 在系統檢視表中全表掃描尋找一條記錄時間）* tab1的記錄條數。其中“在系統檢視表中全表掃描尋找一條記錄時間”會根據tab2的大小而有所不同。若tab2記錄數較小，系統可以直接把表讀到系統區中；若tab2記錄數多，系統無法形成系統檢視表，這時會每一次更新動作，就把子查詢做一次，速度會非常的慢。

針對這種情況的最佳化有兩種

1） 在tab1上的workdate, cino欄位上加入索引，同時增加提示。

修改以後的SQL語句如下：

Update /*+ordered use_nl(sys, t)*/ tab1 tset val1 = 1where (workdate, cino) in (select workdate, cino from tab2)

其中sys表示系統檢視表。如果不加入ordered提示，系統將會預設以tab1表作為驅動表，這時就要對tab1作全表掃描。加入提示後，使用系統檢視表，即select workdate, cino from tab2，作為驅動表，在正常情況下，速度能提高很多。

2） 在tab2表上的workdate, cino欄位加入索引，同時改寫SQL語句：

Update tab1 tset val1 = 1where exists (select 1from tab2where workdate = t.workdateand cino = t.cino)

三，索引問題

update索引的使用比較特殊，有時看起來能用全索引，但實際上卻只用到一部分，所以建議把複合索引的各欄位寫在一起。

例如：

Update /*+ordered use_nl(sys, t)*/ tab1 tset val1 = 1where cino in (select cino from tab2)and workdate = '200506'

這條SQL語句是不能完全用到tab1上的複合索引workdate + cino的。能用到的只是workdate='200506'的約束。

如果寫成這樣，就沒問題：

Update /*+ordered use_nl(sys, t)*/ tab1 tset val1 = 1where (workdate, cino) in (select workdate, cino from tab2)

oracle中的update語句可以用相互關聯的子查詢？

update table1 a set a.name = (select b.name from table2 where a.id=b.id and rownum=1 );

其實要按你的具體需求而定。

步驟多，但效率比較高：
1、create table 暫存資料表 value (select a.id,a.name,b.name,... from table1 a,table2 b where a.id=b.id)
2、刪除table1中的記錄，不要drop
3、insert into table1 select 你需要的欄位 from 暫存資料表。
 
mysql 支援update子查詢？

一般而言，不能更改表，並從子查詢內的相同表進行選擇。
例如，該限制適用於具有下述形式的語句：

DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

例外：如果為FROM子句中更改的表使用子查詢，前述禁令將不再適用。
例如：

UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...)
AS _t ...);