原文:
SQL Server 2012 中 Update FROM子句首先說明一下需求以及環境
建立Table1以及Table2兩張表，并插入一下資料
USE AdventureWorks2012;
GO
IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL
DROP TABLE dbo.Table1;
IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL
DROP TABLE dbo.Table2;
CREATE TABLE dbo.Table1
(ColA int NOT NULL, ColB decimal(10,3) NOT NULL);
CREATE TABLE dbo.Table2
(ColA int PRIMARY KEY NOT NULL, ColB decimal(10,3) NOT NULL);
INSERT INTO dbo.Table1 VALUES(1, 20.0),(2, 30.0);
INSERT INTO dbo.Table2 VALUES(1, 0.1),(2, 0.2);
如何通過一個update語句直接使Table2得到以下結果（僅是奇葩業務需求，請勿吐槽）
可以看出請産生的結果集如使用SELECT 僅需使用簡單INNER JOIN來獲得
SELECT dbo.Table2.ColA,
dbo.Table2.ColB
FROM dbo.Table2
INNER JOIN dbo.Table1
ON (dbo.Table2.ColA = dbo.Table1.ColA);
而對于SQL Server 因為UPDATE 具有From子句 可以寫出如下語句，但強烈不建議使用的此方法來進行
UPDATE dbo.Table2
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
INNER JOIN dbo.Table1
ON (dbo.Table2.ColA = dbo.Table1.ColA);
官方解釋如下
Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.
指定 FROM 子句為更新操作提供條件時務須小心。 如果 UPDATE 語句包含了未指定每個更新列的位置隻有一個可用值的 FROM 子句（換句話說，如果 UPDATE 語句是不确定性的），則其結果将不明确。
Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.
在 SQL Server 的未來版本中，将不再支援在 FROM 子句中使用應用于 UPDATE 或 DELETE 語句目标表的 READUNCOMMITTED 和 NOLOCK 提示。 請避免在新的開發工作上下文中使用這些提示，并計劃修改目前使用它們的應用程式。
個人建議使用MERGE來實作此項功能
MERGE
dbo.Table2
AS
target
USING (SELECT
ColA,ColB
FROM
dbo.Table1)
AS
source
(ColA,COlB)
ON (target.ColA =
source.ColA)
WHEN
MATCHED
THEN
UPDATE
SET
target.ColB = target.ColB +
source.ColB
;
有興趣的同學可以将Table1的資料更改如以下
TRUNCATE
TABLE
table1;
INSERT
INTO
dbo.Table1
VALUES(2, 10.0),
(1, 20.0),(1, 40.0),(2, 30.0);
使用Update後Table2的結果（注意這是一個未确定性的結果集，不代表可以運作處結果結果一定是正确的）
使用MERGE是結果，這個錯誤資訊很好的闡述的這個錯誤的原因
Msg 8672, Level 16, State 1, Line 1
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
Oracle 部分，有空我寫一下
是錯誤的，Oracle中(僅在12c中測試)不允許update多行值對于多行列的更新
http://www.cnblogs.com/JasonLiao/archive/2009/12/23/1630895.htmlUpdate解釋
http://msdn.microsoft.com/en-us/library/ms177523.aspxFROM子句
http://msdn.microsoft.com/en-us/library/ms177523.aspx#OtherTablesMERGE 解釋
http://msdn.microsoft.com/en-us/library/bb510625.aspx
Oracle 中Update
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10008.htm#SQLRF01708