SQL 用一個表的數據更新另一張表

用表B的數據（B1列,B2）更新表A的A1,A2列

SQL Server:

update A SET A.A1 = b.B1,A.A2=B.A2 FROM A ,B WHERE  A.ID1 = B.ID1 and A.ID2 = B.ID2;

Access:

update A, B  set A.A1 = B.B1,A.A2=B.B2 where A.ID1 = B.ID1 and A.ID2 = B.ID2;

--或

update A INNER JOIN B ON A.ID1 = B.ID1 AND A.ID2= B.ID2 SET A.A1 = B.B1,A.A2=B.B2;

通過一條ＳＱＬ語句一次更新多條數據．ＳＱＬ語句寫法略有不同，如下：

方法一:

Update T1
set dc=(select dc1 from t2 where T1.A=T2.A1 AND T1.B=T2.B1)
WHERE T1.AID in (
    SELECT AID FROM T1
    INNER JOIN T2 ON t1.A=T2.A1 AND T1.B=T2.B1
)

注：在我標我紅色的in地方如果把'in'換成'='將出錯，但在下面的Ｂ種寫法里就可以換成'=',變成Ｃ方法寫法.

方法二：

Update T1
set dc=(select dc1 from t2 where T1.A=T2.A1 AND T1.B=T2.B1)
WHERE T1.AID in (
    SELECT T1.AID FROM T2
    WHERE t1.A=T2.A1 AND T1.B=T2.B1
)

方法三：

Update T1
set dc=(select dc1 from t2 where T1.A=T2.A1 AND T1.B=T2.B1)
WHERE T1.AID = (
    SELECT T1.AID FROM T2
    WHERE t1.A=T2.A1 AND T1.B=T2.B1
)

方法四：

Update T1
set dc=(select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1)
WHERE T1.A = (
    SELECT T1.A FROM T2
    WHERE T1.A=T2.A1 AND T1.B=T2.B1
)

方法五：

Update T1
set dc=(select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1)
WHERE T1.A = (
    SELECT T2.A1 FROM T2
    WHERE T1.A=T2.A1 AND T1.B=T2.B1
)

方法六:　

Update T1
set dc=(select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1)
WHERE T1.A in (
    SELECT T2.A1 FROM T2
    WHERE T1.A=T2.A1 AND T1.B=T2.B1
)

注：如果在上面方法改寫成如下的寫法將會出錯。

Update T1 
set dc=(select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1)
WHERE T1.A in (
    SELECT T1.A FROM T1 
    INNER JOIN T2 ON t1.A=T2.A1 AND T1.B=T2.B1
)

此種方法將會更新掉所有符合T1.A in ()這個條件的數據,所以是不正確的, 所以不能這樣寫。

方法七：

Update T1
set dc=(select dc1 from T2 where T1.A=T2.A1 AND T1.B=T2.B1 AND T1.DC=T2.DC1)
FROM T1, T2
WHERE  T1.A=T2.A1 AND T1.B=T2.B1

方法八：

Update T1 
set T1.dc=T2.dc1
FROM T1
INNER JOIN T2 ON T1.A=T2.A1 AND T1.B=T2.B1
WHERE t1.A=T2.A1 AND T1.B=T2.B1

方法九：

Update T1
set T1.dc=T2.dc1
FROM T1, T2
WHERE  T1.A=T2.A1 AND T1.B=T2.B1

請特別注意以上方法中我用紅色標記上的代碼寫法。

總節：雖然是一條簡單的更新語名但也有多種寫法，且每種寫法各有優缺點。

大家是否還有更簡單的寫法或者更容易理解的寫法？如果有請提出來一起討論，謝謝！

說明測試環境：以上代碼在ＭＳＳＱＬ2005中驗證是可行的．

　　注：此處我用灰色標記的部分可以去掉。