Update中的From語句

Oracle\MS SQL Server的資料庫多表關聯更新UPDATE與多表更新【原】

一條Update更新語句是不能更新多張表的，除非使用觸發器隱含更新。而表的更新操作中，在很多情況下需要在運算式中引用要更新的表以外的資料。我們先來討論根據其他表資料更新你要更新的表   

一、MS    SQL    Server   多表關聯更新

     sql server提供了update的from 子句，可以將要更新的表與其它的資料來源串連起來。雖然只能對一個表進行更新，但是通過將要更新的表與其它的資料來源串連起來，就可以在update的運算式 中引用要更新的表以外的其它資料。
     一般形式：

update A SET 欄位1=B表欄位運算式, 欄位2=B表欄位運算式   from B WHERE    邏輯運算式

例如：

    UPDATE dbo.Table2
    SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
    FROM dbo.Table2
    INNER JOIN dbo.Table1
    ON (dbo.Table2.ColA = dbo.Table1.ColA);

 

    實際更新的操作是在要更新的表上進行的，而不是在from子句所形成的新的結果集上進行的

 

二、Oracle     多表關聯更新

     Oracle沒有update from文法，可以通過兩種實現方式：

1、利用子查詢：
     update    A  
     SET    欄位1=（select    欄位運算式    from    B    WHERE    ...）,  
       欄位2=（select    欄位運算式    from    B    WHERE    ...）  
     WHERE    邏輯運算式   

   UPDATE多個欄位兩種寫法：

寫法一：

UPDATE table_1 a
   SET col_x1 = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m),
       col_x2
= (SELECT b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE EXISTS
(SELECT * FROM table_2 b WHERE b.col_n = a.col_m)

或

UPDATE table_1 a
   SET col_x1 = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m),
       col_x2
= (SELECT b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE a.col_m=(SELECT b.col_n FROM table_2 b WHERE b.col_n = a.col_m)

寫法二：

UPDATE table_1 a
   SET (col_x1, col_x2)
= (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE EXISTS (SELECT * FROM table_2 b WHERE b.col_n = a.col_m);

或

UPDATE table_1 a
   SET (col_x1, col_x2) = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE a.col_m=(SELECT b.col_n FROM table_2 b WHERE b.col_n = a.col_m)

 

注意：

    1. 對於子查詢的值只能是一個唯一值，不能是多值。
    2. 子查詢在絕大多數情況下，最後面的where EXISTS子句是重要的，否則將得到錯誤的結果。且where EXISTS子句可用另一方法代替，如上。最後的子句是對a表被更新記錄的限制，如無此句，對於a表中某記錄，如在b表中關聯不到對應的記錄,則該記錄被更新欄位將被更新為null。where
EXISTS子句就是排除對a表中該情況的記錄進行更新。

2、利用視圖：

 

UPDATE (SELECT A.NAME ANAME,B.NAME BNAME FROM A,B WHERE A.ID=B.ID)
SET ANAME=BNAME;

   注意：

    1. 對於視圖更新的限制：
    如果視圖基於多個表的串連，那麼使用者更新（update）視圖記錄的能力將受到限制。除非update只涉及一個表且視圖列中包含了被更新的表的整個主鍵，否則不能更新視圖的基表。

另外，Oracle中的Delete的from子句也沒有多表聯結的功能，只能通過子查詢的方式來做：
delete from 表A where exists (select * from 表B where 表A.empid=表B.empid)
delete from 表A where 表A.empid in (select empid from 表B)

 

三、oracle視圖多表更新

在oracle中通常如果視圖的資料來源來自單表則該視圖可以進行更新。而如果視圖資料來源來自兩個以上表時這個視圖是不可更新的。但有時候為了操作的方便我們更希望能夠對多表視圖也進行更新。

這時候我們可以通過建立更新觸發器來替代該視圖原有更新以達到多表更新的效果

例如：

3.1 建立測試資料表
--===================================================
--建立測試表
--===================================================
Drop Table t1;
Drop Table t2;
create table t1
( t11 numeric(28),t12 varchar2(20));
create table t2
( t11 numeric(28),t22 varchar2(20));

3.2 多表視圖範例
--===================================================
--建立測試檢視
--===================================================
create Or Replace view t as
   select T1.t11 f1 ,T1.t12 f2 ,T2.t22 f3
      from T1,T2
      Where T1.t11=T2.t11;

3.3 多表視圖觸發器範例      
--===================================================
--建立視圖的替代觸發器
--===================================================
Create Or Replace Trigger Trg_InsUpdDel_t
Instead Of Insert or update or delete
on t
for each row
Declare
begin
   If Inserting Then
      Insert Into t1 (t11,t12) Values (:New.f1,:New.f2);
      Insert Into t2 (t11,t22) Values (:New.f1,:New.f3);
   elsif Updating Then
      Update t1 set t11=:New.f1,t12=:New.f2 where t11=:New.f1;
      Update t2 set t11=:New.f1,t22=:New.f3 where t11=:New.f1;
   elsif Deleting then
      Delete from t1 where t11=:Old.f1;
      Delete from t2 where t11=:Old.f1;
   End if;
end;
如此即實現多表可更新視圖的定義工作 。

但要注意當視圖進行重新編譯的時候這個觸發器會失效需要重建。

來自:
http://hi.baidu.com/suofang/blog/item/249c067b40c8def00ad187aa.html

Oracle\MS SQL Server的資料庫多表關聯更新UPDATE與多表更新【原】

一條Update更新語句是不能更新多張表的，除非使用觸發器隱含更新。而表的更新操作中，在很多情況下需要在運算式中引用要更新的表以外的資料。我們先來討論根據其他表資料更新你要更新的表   

一、MS    SQL    Server   多表關聯更新

     sql server提供了update的from 子句，可以將要更新的表與其它的資料來源串連起來。雖然只能對一個表進行更新，但是通過將要更新的表與其它的資料來源串連起來，就可以在update的運算式 中引用要更新的表以外的其它資料。
     一般形式：

update A SET 欄位1=B表欄位運算式, 欄位2=B表欄位運算式   from B WHERE    邏輯運算式

例如：

    UPDATE dbo.Table2
    SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
    FROM dbo.Table2
    INNER JOIN dbo.Table1
    ON (dbo.Table2.ColA = dbo.Table1.ColA);

 

    實際更新的操作是在要更新的表上進行的，而不是在from子句所形成的新的結果集上進行的

 

二、Oracle     多表關聯更新

     Oracle沒有update from文法，可以通過兩種實現方式：

1、利用子查詢：
     update    A  
     SET    欄位1=（select    欄位運算式    from    B    WHERE    ...）,  
       欄位2=（select    欄位運算式    from    B    WHERE    ...）  
     WHERE    邏輯運算式   

   UPDATE多個欄位兩種寫法：

寫法一：

UPDATE table_1 a
   SET col_x1 = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m),
       col_x2
= (SELECT b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE EXISTS
(SELECT * FROM table_2 b WHERE b.col_n = a.col_m)

或

UPDATE table_1 a
   SET col_x1 = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m),
       col_x2
= (SELECT b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE a.col_m=(SELECT b.col_n FROM table_2 b WHERE b.col_n = a.col_m)

寫法二：

UPDATE table_1 a
   SET (col_x1, col_x2)
= (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE EXISTS (SELECT * FROM table_2 b WHERE b.col_n = a.col_m);

或

UPDATE table_1 a
   SET (col_x1, col_x2) = (SELECT b.col_y1, b.col_y2 FROM table_2 b WHERE b.col_n = a.col_m)
WHERE a.col_m=(SELECT b.col_n FROM table_2 b WHERE b.col_n = a.col_m)

 

注意：

    1. 對於子查詢的值只能是一個唯一值，不能是多值。
    2. 子查詢在絕大多數情況下，最後面的where EXISTS子句是重要的，否則將得到錯誤的結果。且where EXISTS子句可用另一方法代替，如上。最後的子句是對a表被更新記錄的限制，如無此句，對於a表中某記錄，如在b表中關聯不到對應的記錄,則該記錄被更新欄位將被更新為null。where
EXISTS子句就是排除對a表中該情況的記錄進行更新。

2、利用視圖：

 

UPDATE (SELECT A.NAME ANAME,B.NAME BNAME FROM A,B WHERE A.ID=B.ID)
SET ANAME=BNAME;

   注意：

    1. 對於視圖更新的限制：
    如果視圖基於多個表的串連，那麼使用者更新（update）視圖記錄的能力將受到限制。除非update只涉及一個表且視圖列中包含了被更新的表的整個主鍵，否則不能更新視圖的基表。

另外，Oracle中的Delete的from子句也沒有多表聯結的功能，只能通過子查詢的方式來做：
delete from 表A where exists (select * from 表B where 表A.empid=表B.empid)
delete from 表A where 表A.empid in (select empid from 表B)

 

三、oracle視圖多表更新

在oracle中通常如果視圖的資料來源來自單表則該視圖可以進行更新。而如果視圖資料來源來自兩個以上表時這個視圖是不可更新的。但有時候為了操作的方便我們更希望能夠對多表視圖也進行更新。

這時候我們可以通過建立更新觸發器來替代該視圖原有更新以達到多表更新的效果

例如：

3.1 建立測試資料表
--===================================================
--建立測試表
--===================================================
Drop Table t1;
Drop Table t2;
create table t1
( t11 numeric(28),t12 varchar2(20));
create table t2
( t11 numeric(28),t22 varchar2(20));

3.2 多表視圖範例
--===================================================
--建立測試檢視
--===================================================
create Or Replace view t as
   select T1.t11 f1 ,T1.t12 f2 ,T2.t22 f3
      from T1,T2
      Where T1.t11=T2.t11;

3.3 多表視圖觸發器範例      
--===================================================
--建立視圖的替代觸發器
--===================================================
Create Or Replace Trigger Trg_InsUpdDel_t
Instead Of Insert or update or delete
on t
for each row
Declare
begin
   If Inserting Then
      Insert Into t1 (t11,t12) Values (:New.f1,:New.f2);
      Insert Into t2 (t11,t22) Values (:New.f1,:New.f3);
   elsif Updating Then
      Update t1 set t11=:New.f1,t12=:New.f2 where t11=:New.f1;
      Update t2 set t11=:New.f1,t22=:New.f3 where t11=:New.f1;
   elsif Deleting then
      Delete from t1 where t11=:Old.f1;
      Delete from t2 where t11=:Old.f1;
   End if;
end;
如此即實現多表可更新視圖的定義工作 。

但要注意當視圖進行重新編譯的時候這個觸發器會失效需要重建。

來自:
http://hi.baidu.com/suofang/blog/item/249c067b40c8def00ad187aa.html