UPDATE (Transact-SQL)

在 SQL Server 2008 中，變更資料表或檢視表內一個或多個資料行的現有資料。

Transact-SQL 語法慣例

語法

[ WITH common_table_expression [...n] ]
UPDATE 
    [ TOP (expression) [ PERCENT ] ] 
    { { table_alias | <object> | rowset_function_limited 
        [ WITH (table_hint_limited [ ...n ] ) ]
      }
      | @table_variable
    }
    SET
        { column_name= { expression | DEFAULT | NULL }
          | { udt_column_name.{ { property_name=expression
                                | field_name=expression }
                                | method_name(argument [ ,...n ] )
                              }
          }
          | column_name { .WRITE (expression,@Offset,@Length) }
          | @variable=expression
          | @variable=column=expression
          | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
          | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
          | @variable=column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
        } [ ,...n ] 

    [ OUTPUT clause ]
    [ FROM { table_source } [ ,...n ] ] 
    [ WHERE { search_condition 
            | { [ CURRENT OF 
                  { { [ GLOBAL ] cursor_name } 
                      | cursor_variable_name 
                  } 
                ]
              }
            } 
    ] 
    [ OPTION (query_hint [ ,...n ] ) ]
[ ; ]

<object> ::=
{ 
    [ server_name .database_name.schema_name. 
    | database_name .[ schema_name ] . 
    | schema_name.
    ]
    table_or_view_name
}

引數

• WITH common_table_expression
  指定定義在 UPDATE 陳述式範圍內的暫存具名結果集或檢視表，也稱為通用資料表運算式 (CTE)。此結果集衍生自 SELECT 陳述式，而且由 UPDATE 陳述式所參考。如需詳細資訊，請參閱＜WITH common_table_expression (Transact-SQL)＞。

• TOP ( expression) [ PERCENT ]
  指定將更新的資料列數目或百分比。expression 可以是一個數字，也可以是資料列的百分比。在 INSERT、UPDATE、MERGE 或 DELETE 之 TOP 運算式中參考的資料列並不會依照任何順序來排列。

  TOP 中用來分隔 expression 的括號，在 INSERT、UPDATE、MERGE 和 DELETE 陳述式中是必要的。如需詳細資訊，請參閱＜TOP (Transact-SQL)＞。

• table_alias
  在 FROM 子句中指定的別名，代表要更新資料列的資料表或檢視表。

• server_name
  這是資料表或檢視表所在之連結伺服器的名稱。server_name 可以指定為連結的伺服器名稱，或使用 OPENDATASOURCE 函數來指定。

  當 server_name 指定為連結的伺服器時，database_name 和 schema_name 就是必要參數。當 server_name 是使用 OPENDATASOURCE 來指定時，database_name 和 schema_name 可能無法套用至所有資料來源，而且受限於存取遠端物件之 OLE DB 提供者的功能。如需詳細資訊，請參閱＜分散式查詢＞。

• database_name
  資料庫的名稱。

• schema_name
  資料表或檢視表所屬之結構描述的名稱。

• table_or view_name
  要更新資料列之資料表或檢視表的名稱。

  table_or_view_name 所參考的檢視表必須能夠更新，而且必須只參考檢視表定義之 FROM 子句中的一個基底資料表。如需有關可更新檢視表的詳細資訊，請參閱＜CREATE VIEW (Transact-SQL)＞。

• rowset_function_limited
  這是 OPENQUERY 或 OPENROWSET 函數。這些函數的使用方式受限於存取遠端物件之 OLE DB 提供者的功能。如需詳細資訊，請參閱＜分散式查詢＞。

• WITH (table_hint_limited)
  指定目標資料表允許使用的一個或多個資料表提示。在 UPDATE 陳述式的持續期間內，資料表提示會覆寫查詢最佳化工具的預設行為。不允許使用 NOLOCK 和 READUNCOMMITTED。如需有關資料表提示的詳細資訊，請參閱＜資料表提示 (Transact-SQL)＞。

• @table\_variable
  將資料表變數指定為資料表來源。

• SET
  指定要更新的資料行或變數名稱清單。

• column_name
  這是包含要變更之資料的資料行。column_name 必須在 table_or view_name 中。無法更新識別欄位。

• expression
  這是傳回單一值的變數、常值、運算式或子選取陳述式 (括在括號內)。expression 傳回的值會取代 column_name 或 @variable 中現有的值。

  [!附註]

  當參考 Unicode 字元資料類型 nchar、nvarchar 和 ntext 時，'expression' 的前面應該要有大寫字母 'N'。如果沒有指定 'N'，SQL Server 會將字串轉換成對應至資料庫預設定序或資料行的字碼頁。在此字碼頁中找不到的任何字元都會遺失。如需詳細資訊，請參閱＜使用 Unicode 資料＞。

• DEFAULT
  指定資料行所定義的預設值要取代資料行中現有的值。如果資料行沒有預設值，且定義成允許空值，您可以利用這個方式，將資料行改成 NULL。

• { += | -= | *= | /= | %= | &= | ^= | |= }
  這是複合運算子，可用來執行作業，並且將原始值設定為該作業的結果。

  +=    相加並指派

  -=    相減並指派

  *=    相乘並指派

  /= 相除並指派

  %= 模數並指派

  &=    位元 AND 並指派

  ^=    位元 XOR 並指派

  |=    位元 OR 並指派

  如需詳細資訊，請參閱＜複合運算子 (Transact-SQL)＞。

• udt_column_name
  這是使用者定義型別資料行。

• property_name | field_name
  這是使用者定義型別的公用屬性或公用資料成員。提供值的運算式必須可隱含地轉換成屬性的類型。

  若要修改相同使用者定義型別資料行的不同屬性，請發出多個 UPDATE 陳述式，或呼叫該類型的 mutator 方法。

• method_name( argument [ ,...n] )
  這是採用一個或多個引數之 udt_column_name 的非靜態公用 mutator 方法。如果在 Transact-SQL Null 值上叫用了 mutator 方法，或是 mutator 方法所產生的新值是 Null，SQL Server 就會傳回錯誤。

• .WRITE (expression,@Offset, @Length)
  指定要修改 column_name 值的區段。expression 取代開頭為 column_name 之 @Offset 的 @Length 單位。這個子句只能指定 varchar(max)、nvarchar(max) 或 varbinary(max) 的資料行。column_name 不能是 NULL，也不能用資料表名稱或資料表別名來限定。

  expression 是複製到 column_name 的值。expression 必須評估為或能夠隱含轉換為 column_name 類型。如果 expression 設定為 NULL，會忽略 @Length，且會在指定的 @Offset 中截斷 column_name 中的值。

  @Offset 是 column_name 值中的起點，而 expression 則是在該起點寫入的。@Offset 是以零為基底的序數位置，也是 bigint，且不能是負數。如果 @Offset 是 NULL，更新作業會在現有 column_name 值的結尾附加 expression，且會忽略 @Length。如果 @Offset 大於 column_name 值的長度，Database Engine 會傳回一則錯誤。如果 @Offset 加上 @Length 超出資料行基礎值的結尾，就會刪除到值的最後一個字元。如果 @Offset 加上 LEN(expression) 大於基礎的宣告大小，就會引發錯誤。

  @Length 是資料行中的區段長度，開頭為 @Offset，它會由 expression 所取代。@Length 是 bigint，且不能是負數。如果 @Length 是 NULL，更新作業會移除從 @Offset 到 column_name 值結尾的所有資料。

  如需詳細資訊，請參閱「更新大數值資料類型」。

• @variable
  這是設定為 expression 傳回之值的宣告變數。

  SET @variable = column = expression 會將變數設成與資料行相同的值。這有別於 SET @variable = column、column = expression，它會將變數設成資料行更新之前的值。

• OUTPUT 子句
  在 UPDATE 作業中，傳回更新資料或以更新資料為基礎的運算式。在以本機資料分割檢視表、分散式資料分割檢視表、遠端資料表或遠端檢視表為目標的任何 DML 陳述式中，都不支援 OUTPUT 子句。如需詳細資訊，請參閱＜OUTPUT 子句 (Transact-SQL)＞。

• FROM table_source
  指定利用資料表、檢視表或衍生資料表來源來提供更新作業的準則。如需詳細資訊，請參閱＜FROM (Transact-SQL)＞。

  如果更新的物件與 FROM 子句中的物件相同，且只有一個參考指向 FROM 子句中的物件，就不一定要指定物件別名。如果更新的物件在 FROM 子句中重複出現，就正好只有一個指向這個物件的參考不能指定資料表別名。所有其他指向 FROM 子句中之物件的參考，都必須包括物件別名。

• WHERE
  指定用來限制更新資料列的條件。根據所用的 WHERE 子句形式，更新有兩種形式：

  • 搜尋的更新會指定用來限定要刪除之資料列的搜尋條件。

  • 定位更新利用 CURRENT OF 子句來指定資料指標。更新作業發生在資料指標目前的位置上。利用 WHERE CURRENT OF 子句來進行的定位更新，會在資料指標的目前位置更新單一資料列。這比利用 WHERE <search_condition> 子句來限定要更新之資料列的搜尋更新還要精確。當搜尋條件並未唯一識別單一資料列時，搜尋更新會修改多個資料列。

• search_condition
  指定要更新之資料列的相符條件。搜尋條件也可以是聯結的基礎條件。搜尋條件中所能包括的述詞數目沒有限制。如需有關述詞和搜尋條件的詳細資訊，請參閱＜搜尋條件 (Transact-SQL)＞。

• CURRENT OF
  指定在指定資料指標目前的位置執行更新。

• GLOBAL
  指定 cursor_name 參考全域資料指標。

• cursor_name
  應該從中提取的開啟資料指標名稱。如果名稱為 cursor_name 的全域和區域資料指標同時存在，當指定了 GLOBAL 時，這個引數會參考全域資料指標，否則，它會參考區域資料指標。這個資料指標必須允許更新。

• cursor_variable_name
  資料指標變數的名稱。cursor_variable_name 必須參考允許更新的資料指標。

• OPTION (query_hint [ ,... n] )
  指定利用最佳化工具提示來自訂 Database Engine 處理陳述式的方式。如需詳細資訊，請參閱＜查詢提示 (Transact-SQL)＞。

最佳作法

UPDATE 陳述式可以利用變數名稱來顯示受影響的舊值和新值，但這只適用於 UPDATE 陳述式會影響單一記錄的情況。如果 UPDATE 陳述式會影響多項記錄，請使用 OUTPUT 子句來傳回各項記錄的舊值和新值。

指定 FROM 子句來提供更新作業的準則時，請特別小心。如果 UPDATE 陳述式包括 FROM 子句，且這個 FROM 子句的指定方式並非每個更新的資料行項目都只能使用一個值，也就是說，如果 UPDATE 陳述式不具決定性，UPDATE 陳述式的結果便未定義。這可能會造成非預期的結果。例如，在下列指令碼的 UPDATE 陳述式中，Table1 中的兩個資料列都符合 UPDATE 陳述式中之 FROM 子句的識別資格；但利用 Table1 中的哪個資料列來更新 Table2 中的資料列，並未定義。

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL
    DROP TABLE dbo.Table1;
GO
IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL
    DROP TABLE dbo.Table2;
GO
CREATE TABLE dbo.Table1 
    (ColA int PRIMARY KEY NOT NULL, ColB decimal(10,3) NOT NULL);
GO
CREATE TABLE dbo.Table2 
    (ColA int PRIMARY KEY NOT NULL, ColB decimal(10,3) NOT NULL);
GO
INSERT INTO dbo.Table1 VALUES (1, 10.0), (1, 20.0);
INSERT INTO dbo.Table2 VALUES (1, 0.0);
;
GO

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);
GO
SELECT ColA, ColB 
FROM dbo.Table2;

當組合 FROM 和 WHERE CURRENT OF 子句時，也會出現相同的問題。在下列範例中，Table2 中的兩個資料列都符合 UPDATE 陳述式中之 FROM 子句的識別資格。利用 Table2 中的哪個資料列來更新 Table1 中的資料列，並未定義。

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL
    DROP TABLE dbo.Table1;
GO
IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL
    DROP TABLE dbo.Table2;
GO
CREATE TABLE dbo.Table1
    (c1 int PRIMARY KEY NOT NULL, c2 int NOT NULL);
GO
CREATE TABLE dbo.Table2
    (d1 int PRIMARY KEY NOT NULL, d2 int NOT NULL);
GO
INSERT INTO dbo.Table1 VALUES (1, 10);
INSERT INTO dbo.Table2 VALUES (1, 20), (2, 30);
GO

DECLARE abc CURSOR LOCAL FOR
    SELECT c1, c2 
    FROM dbo.Table1;
OPEN abc;
FETCH abc;
UPDATE dbo.Table1 
SET c2 = c2 + d2 
FROM dbo.Table2 
WHERE CURRENT OF abc;
GO
SELECT c1, c2 FROM dbo.Table1;
GO

資料類型

所有 char 和 nchar 資料行都會向右填補到定義的長度。

如果 ANSI_PADDING 設為 OFF，便會從插入 varchar 和 nvarchar 資料行的資料中移除所有尾端空格，但只含有空格的字串除外。這些字串會截斷成空字串。如果 ANSI_PADDING 設為 ON，便會插入尾端空格。Microsoft SQL Server ODBC 驅動程式和 OLE DB Provider for SQL Server 會自動設定每項連接的 ANSI_PADDING ON。您可以在 ODBC 資料來源中設定這個項目，也可以設定連接屬性來設定這個項目。如需詳細資訊，請參閱＜SET ANSI_PADDING (Transact-SQL)＞。

更新大數值資料類型

請利用 .WRITE (expression, @Offset,@Length) 子句來執行 varchar(max)、nvarchar(max) 及 varbinary(max) 資料類型的部分或完整更新。例如，部分更新 varchar(max) 資料行可能只刪除或修改資料行的前 200 個字元，完整更新則會刪除或修改資料行中的所有資料。

若要有最佳效能，我們建議您以 8040 位元組倍數的片段大小來插入或更新資料。

當 UPDATE 陳述式造成下列情況時，Database Engine 會將部分更新轉換成完整更新：

• 變更資料分割檢視或資料表的索引鍵資料行。

• 修改多個資料列，同時也將不是唯一的叢集索引之索引鍵更新成非常數值。

您不能利用 .WRITE 子句來更新 NULL 資料行，或將 column_name 的值設成 NULL。

varbinary 和 varchar 資料類型的 @Offset 和 @Length 是以位元組來指定，nvarchar 資料類型則是以字元來指定。雙位元組字集 (DBCS) 定序會計算適當的位移。

如果在 OUTPUT 子句中參考 .WRITE 子句所修改的資料行，就會將資料行的完整值 (不論是在 deleted.column_name 中的影像之前，或在 inserted.column_name 中的影像之後) 傳回資料表變數中的指定資料行。

若要利用其他字元或二進位資料類型來完成 .WRITE 的相同功能，請使用 STUFF (Transact-SQL)。

更新 FILESTREAM 資料

您可以使用 UPDATE 陳述式，將 FILESTREAM 欄位更新為 Null 值、空白值，或是相當少量的內嵌資料。但是，使用 Win32 介面，將大量資料當做資料流處理成檔案時，會比較有效率。當您更新 FILESTREAM 欄位時，您會修改檔案系統中的基礎 BLOB 資料。當 FILESTREAM 欄位設定為 NULL 時，與此欄位有關聯的 BLOB 資料會遭到刪除。您無法使用 .WRITE() 來執行 FILESTREAM 資料的部分更新。如需詳細資訊，請參閱＜FILESTREAM 概觀＞。

更新 text、ntext 和 image 資料行

除非用 NULL 更新資料行，否則，利用 UPDATE 來修改 text、ntext 或 image 資料行會初始化資料行、將有效的文字指標指派給它，再配置至少一個資料頁面。如果 UPDATE 陳述式在更新叢集索引鍵及一或多個 text、 ntext 或 image 資料行時，可以變更多個資料列，以完整取代值的方式來執行這些資料列的部分更新。

若要取代或修改 text、ntext 或 image 資料的大型區塊，請利用 WRITETEXT 或 UPDATETEXT 來取代 UPDATE 陳述式。

重要事項
未來的 MicrosoftSQL Server 版本將移除 ntext、text 和 image 等資料類型。請避免在新的開發工作中使用這些資料類型，並規劃修改目前在使用這些資料類型的應用程式。請改用 nvarchar(max)、varchar(max) 和 varbinary(max)。如需詳細資訊，請參閱＜使用大數值資料類型＞。

錯誤處理

您可以在 TRY…CATCH 建構中指定 UPDATE 陳述式，藉以實作此陳述式的錯誤處理。如需詳細資訊，請參閱＜在 Transact-SQL 中使用 TRY...CATCH＞。

如果資料列的更新違反條件約束或規則、違反資料行的 NULL 設定，或新值是不相容的資料類型，便會取消陳述式，傳回錯誤，且不會更新任何記錄。

當 UPDATE 陳述式在運算式評估期間發生算術錯誤 (溢位、除以零或區域錯誤) 時，便不會進行更新。批次的其餘部分也不會執行，且會傳回錯誤訊息。

如果參與叢集索引的一個或多個資料行的更新使叢集索引和資料列的大小超出 8,060 位元組，更新就會失敗，且會傳回錯誤訊息。

互通性

只有當所修改的資料表是一個資料表變數時，才能在使用者定義函數的主體中使用 UPDATE 陳述式。

當定義資料表之 UPDATE 動作的 INSTEAD OF 觸發程序時，會執行觸發程序，而不是 UPDATE 陳述式。舊版的 SQL Server 只支援 UPDATE 及其他資料修改陳述式所定義的 AFTER 觸發程序。

限制事項

在直接或間接參考定義了 INSTEAD OF 觸發程序之檢視表的 UPDATE 陳述式中，不能指定 FROM 子句。如需有關 INSTEAD OF 觸發程序的詳細資訊，請參閱＜CREATE TRIGGER (Transact-SQL)＞。

針對遠端資料表及本機和遠端資料分割檢視來進行的 UPDATE 陳述式，其 SET ROWCOUNT 選項的設定會被忽略。使用 SET ROWCOUNT 不會影響 SQL Server 下一版本中的 UPDATE 陳述式。請勿在新的開發工作中使用 SET ROWCOUNT 搭配 UPDATE 陳述式，並請將目前使用它的應用程式修改成使用 TOP 語法。

當通用資料表運算式 (CTE) 是 UPDATE 陳述式的目標時，陳述式中所有 CTE 的參考都必須相符。例如，如果 CTE 被指派 FROM 子句中的別名，此別名就必須用於 CTE 的所有其他參考。因為 CTE 沒有物件識別碼，可讓 SQL Server 用來辨識物件與其別名之間的隱含關聯性，所以需要使用不模稜兩可的 CTE 參考。如果沒有這個關聯性，查詢計畫可能會產生無法預期的聯結行為和不想要的查詢結果。 下列範例將示範當 CTE 是更新作業的目標物件時，指定 CTE 的正確與不正確方法。

USE tempdb;
GO
-- UPDATE statement with CTE references that are correctly matched.
DECLARE @x TABLE (ID int, Value int);
DECLARE @y TABLE (ID int, Value int);
INSERT @x VALUES (1, 10), (2, 20);
INSERT @y VALUES (1, 100),(2, 200);

WITH cte AS (SELECT * FROM @x)
UPDATE x -- cte is referenced by the alias.
SET Value = y.Value
FROM cte AS x  -- cte is assigned an alias.
INNER JOIN @y AS y ON y.ID = x.ID;
SELECT * FROM @x;
GO

以下為結果集：

ID     Value

------ -----

1      100

2      200

(2 row(s) affected)

-- UPDATE statement with CTE references that are incorrectly matched.
USE tempdb;
GO
DECLARE @x TABLE (ID int, Value int);
DECLARE @y TABLE (ID int, Value int);
INSERT @x VALUES (1, 10), (2, 20);
INSERT @y VALUES (1, 100),(2, 200);

WITH cte AS (SELECT * FROM @x)
UPDATE cte   -- cte is not referenced by the alias.
SET Value = y.Value
FROM cte AS x  -- cte is assigned an alias.
INNER JOIN @y AS y ON y.ID = x.ID; 
SELECT * FROM @x; 
GO

以下為結果集：

ID     Value

------ -----

1      100

2      100

(2 row(s) affected)

記錄行為

UPDATE 陳述式會採用完整記錄。不過，如果資料庫復原模式設為大量記錄或簡單模式，插入或附加新資料的 .WRITE 更新就會採用最低限度記錄。當 .WRITE 用來更新現有的值時，不會使用最低限度記錄。如需詳細資訊，請參閱＜可以進行最低限度記錄的作業＞。

安全性

權限

需要目標資料表的 UPDATE 權限。如果 UPDATE 陳述式包含 WHERE 子句，或 SET 子句中的 expression 使用資料表中的資料行，則需要所更新之資料表的 SELECT 權限。

UPDATE 權限預設為 sysadmin 固定伺服器角色、db_owner 和 db_datawriter 固定資料庫角色的成員，以及資料表擁有者。sysadmin、db_owner 和 db_securityadmin 角色的成員及資料表擁有者可將權限移轉給其他使用者。

範例

類別目錄	代表性語法元素
基本語法	UPDATE
限制更新的資料列	WHERE • TOP • WITH 通用資料表運算式 • WHERE CURRENT OF
設定資料行值	計算 • 複合運算子 • 預設值 • 子查詢
指定標準資料表以外的目標物件	檢視表 • 資料表變數 • 資料表別名
根據其他資料表的資料更新資料	FROM
更新遠端資料表中的資料列	連結的伺服器 • OPENQUERY • OPENDATASOURCE
更新大型物件資料類型	.WRITE • OPENROWSET
更新使用者定義型別	使用者定義型別
使用提示來覆寫查詢最佳化工具的預設行為	資料表提示 • 查詢提示
擷取 UPDATE 陳述式的結果	OUTPUT 子句
在其他陳述式中使用 UPDATE	預存程序 • TRY…CATCH

基本語法

本節的範例會使用所需的最少語法來示範 UPDATE 陳述式的基本功能。

A. 使用簡單的 UPDATE 陳述式

下列範例會更新 Person.Address 資料表中所有資料列的單一資料行。

USE AdventureWorks;
GO
UPDATE Person.Address
SET ModifiedDate = GETDATE();

B. 更新多個資料行

下列範例會更新 SalesPerson 資料表中所有資料列的 Bonus、CommissionPct 和 SalesQuota 資料行值。

USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET Bonus = 6000, CommissionPct = .10, SalesQuota = NULL;
GO

限制更新的資料列

本節的範例會示範您可以用來限制受到 UPDATE 陳述式所影響之資料列數目的方式。

A. 使用 WHERE 子句

下列範例會利用 WHERE 子句來指定要更新的資料列。此陳述式會針對 Color 資料行的現有值為 'Red' 而且 Name 資料行值以 'Road-250' 為開頭的所有資料列更新 Production.Product 資料表的 Color 資料行值。

USE AdventureWorks;
GO
UPDATE Production.Product
SET Color = N'Metallic Red'
WHERE Name LIKE N'Road-250%' AND Color = N'Red';
GO

B. 使用 TOP 子句

下列範例會使用 TOP 子句，限制 UPDATE 陳述式中可修改的資料列數。當 TOP (n) 子句與 UPDATE 一起使用時，系統會隨機選取 n 個資料列來執行更新作業。下列範例會將 Employee 資料表中 10 個隨機資料列的 VacationHours 資料行更新 25%。

USE AdventureWorks;
GO
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25 ;
GO

如果您必須使用 TOP 依有意義的時間順序套用更新，就要在子選擇陳述式中同時使用 TOP 與 ORDER BY。下例會更新最早雇用的前 10 名員工的休假時數。

UPDATE HumanResources.Employee
SET VacationHours = VacationHours + 8
FROM (SELECT TOP 10 EmployeeID FROM HumanResources.Employee
     ORDER BY HireDate ASC) AS th
WHERE HumanResources.Employee.EmployeeID = th.EmployeeID;
GO

C. 使用 WITH common_table_expression 子句

下列範例會將直接或間接向 ManagerID12 提出報告之所有員工的 VacationHours 值更新 25%。通用資料表運算式會傳回一份階層式員工清單，其中包括直接向 ManagerID12 提出報告的員工，以及向這些員工提出報告的員工，依此類推。只會修改通用資料表運算式所傳回的資料列。如需有關遞迴通用資料表運算式的詳細資訊，請參閱＜使用一般資料表運算式的遞迴查詢＞。

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

D. 使用 WHERE CURRENT OF 子句

下列範例會使用 WHERE CURRENT OF 子句來單獨更新資料指標所在的資料列。當資料指標是以聯結為基礎時，只會修改在 UPDATE 陳述式中指定的 table_name。牽涉到資料指標的其他資料表都不會受到影響。

USE AdventureWorks;
GO
DECLARE complex_cursor CURSOR FOR
    SELECT a.EmployeeID
    FROM HumanResources.EmployeePayHistory AS a
    WHERE RateChangeDate <> 
         (SELECT MAX(RateChangeDate)
          FROM HumanResources.EmployeePayHistory AS b
          WHERE a.EmployeeID = b.EmployeeID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
UPDATE HumanResources.EmployeePayHistory
SET PayFrequency = 2 
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;
GO

設定資料行值

本節的範例會示範如何使用計算值、子查詢和 DEFAULT 值來更新資料行。

A. 指定計算值

下列範例會在 UPDATE 陳述式中使用計算值。此範例會將 Product 資料表中所有資料列的 ListPrice 資料行值加倍。

USE AdventureWorks ;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2;
GO

B. 指定複合運算子

下列範例會使用 @NewPrice 變數來遞增所有紅色自行車的價格，其方式是將目前的價格增加 10。

USE AdventureWorks;
GO
DECLARE @NewPrice int = 10;
UPDATE Production.Product
SET ListPrice += @NewPrice
WHERE Color = N'Red';
GO

下列範例會針對 ScrapReasonID 介於 10 到 12 之間的資料列，使用複合運算子 +=，將 ' - tool malfunction' 資料附加至 Name 資料行中的現有值。

USE AdventureWorks;
GO
UPDATE Production.ScrapReason 
SET Name += ' - tool malfunction'
WHERE ScrapReasonID BETWEEN 10 and 12;

C. 在 SET 子句中指定子查詢

下列範例會使用 SET 子句中的子查詢來判斷用於更新資料行的值。這個子查詢必須只傳回純量值 (亦即，每個資料列的單一值)。此範例會修改 SalesPerson 資料表中的 SalesYTD 資料行，以便反映 SalesOrderHeader 資料表中最新的銷售記錄。這個子查詢將彙總 UPDATE 陳述式中每位銷售人員的銷售額。

USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + 
    (SELECT SUM(so.SubTotal) 
     FROM Sales.SalesOrderHeader AS so
     WHERE so.OrderDate = (SELECT MAX(OrderDate)
                           FROM Sales.SalesOrderHeader AS so2
                           WHERE so2.SalesPersonID = 
                                 so.SalesPersonID)
     AND Sales.SalesPerson.SalesPersonID = so.SalesPersonID
     GROUP BY so.SalesPersonID);
GO

D. 使用 DEFAULT 值更新資料列

下列範例會針對 CostRate 值大於 20.00 的所有資料列，將 CostRate 資料行設定為其預設值 (0.00)。

USE AdventureWorks;
GO
UPDATE Production.Location
SET CostRate = DEFAULT
WHERE CostRate > 20.00;

指定標準資料表以外的目標物件

本節的範例會示範如何透過指定檢視表、資料表別名或資料表變數，更新資料列。

A. 將檢視表指定為目標物件

下列範例會透過將檢視表指定為目標物件，更新資料表中的資料列。雖然檢視表定義會參考多個資料表，不過 UPDATE 陳述式仍然會成功，因為它只參考其中一個基礎資料表中的資料行。如果同時指定了兩個資料表中的資料行，UPDATE 陳述式就會失敗。如需詳細資訊，請參閱＜透過檢視修改資料＞。

USE AdventureWorks;
GO
UPDATE Person.vStateProvinceCountryRegion
SET CountryRegionName = 'United States of America'
WHERE CountryRegionName = 'United States';

B. 將資料表別名指定為目標物件

下列範例會更新 Production.ScrapReason 資料表中的資料列。在 FROM 子句中，指派給 ScrapReason 的資料表別名會指定為 UPDATE 子句中的目標物件。

USE AdventureWorks;
GO
UPDATE sr
SET sr.Name += ' - tool malfunction'
FROM Production.ScrapReason AS sr
JOIN Production.WorkOrder AS wo 
     ON sr.ScrapReasonID = wo.ScrapReasonID
     AND wo.ScrappedQty > 300;

C. 將資料表變數指定為目標物件

下列範例會更新資料表變數中的資料列。

USE AdventureWorks;
GO
-- Create the table variable.
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    NewVacationHours int,
    ModifiedDate datetime);

-- Populate the table variable with employee ID values from HumanResources.Employee.
INSERT INTO @MyTableVar (EmpID)
    SELECT EmployeeID FROM HumanResources.Employee;

-- Update columns in the table variable.
UPDATE @MyTableVar
SET NewVacationHours = e.VacationHours + 20,
    ModifiedDate = GETDATE()
FROM HumanResources.Employee AS e 
WHERE e.EmployeeID = EmpID;

-- Display the results of the UPDATE statement.
SELECT EmpID, NewVacationHours, ModifiedDate FROM @MyTableVar
ORDER BY EmpID;
GO

根據其他資料表的資料更新資料

本節的範例會示範如何根據某個資料表中的資訊更新另一個資料表中的資料列。

A. 搭配另一個資料表的資訊來使用 UPDATE 陳述式

下列範例會修改 SalesPerson 資料表中的 SalesYTD 資料行，以便反映 SalesOrderHeader 資料表中最新的銷售記錄。

USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + SubTotal
FROM Sales.SalesPerson AS sp
JOIN Sales.SalesOrderHeader AS so
    ON sp.SalesPersonID = so.SalesPersonID
    AND so.OrderDate = (SELECT MAX(OrderDate)
                        FROM Sales.SalesOrderHeader 
                        WHERE SalesPersonID = 
                              sp.SalesPersonID);
GO

上一個範例假設指定之銷售人員在特定日期只有一項銷售記錄，且更新是最新的。如果指定之銷售人員同一天可以有多項銷售記錄，顯示的範例便無法正確運作。這個範例執行無誤，但每個 SalesYTD 值都只用一項銷售來更新，不論當天實際上有多少銷售項目都是如此。這是因為單一 UPDATE 陳述式永遠不會更新相同資料列兩次。

如果在同一天內，指定的銷售人員可以有多筆銷售額，則必須在 UPDATE 陳述式內彙總每一個銷售人員的所有銷售額，如下列範例所示：

USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + 
    (SELECT SUM(so.SubTotal) 
     FROM Sales.SalesOrderHeader AS so
     WHERE so.OrderDate = (SELECT MAX(OrderDate)
                           FROM Sales.SalesOrderHeader AS so2
                           WHERE so2.SalesPersonID = 
                                 so.SalesPersonID)
     AND Sales.SalesPerson.SalesPersonID = so.SalesPersonID
     GROUP BY so.SalesPersonID);
GO

更新遠端資料表中的資料列

本節的範例示範如何更新遠端目標資料表中的資料列，其方式是使用連結的伺服器或資料列集函數參考遠端資料表。

A. 使用連結的伺服器更新遠端資料表中的資料

下列範例會更新遠端伺服器上的資料表。此範例一開始會使用 sp_addlinkedserver 建立遠端資料來源的連結。然後會將連結的伺服器名稱 MyLinkServer 指定為 server.catalog.schema.object 格式之四部分物件名稱的一部分。請注意，您必須針對 @datasrc 指定有效的伺服器名稱。

USE master;
GO
-- Create a link to the remote data source. 
-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.

EXEC sp_addlinkedserver @server = N'MyLinkServer',
    @srvproduct = N' ',
    @provider = N'SQLNCLI10', 
    @datasrc = N'<server name>',
    @catalog = N'AdventureWorks';
GO
USE AdventureWorks;
GO
-- Specify the remote data source using a four-part name 
-- in the form linked_server.catalog.schema.object.

UPDATE MyLinkServer.AdventureWorks.HumanResources.Department
SET GroupName = N'Public Relations'
WHERE DepartmentID = 4;

B. 使用 OPENQUERY 函數更新遠端資料表中的資料

下列範例會藉由指定 OPENQUERY 資料列集函數，更新遠端資料表中的資料。上一個範例所建立之連結的伺服器名稱會用於這個範例。

UPDATE OPENQUERY (MyLinkServer, 'SELECT GroupName FROM HumanResources.Department WHERE DepartmentID = 4') 
SET GroupName = 'Sales and Marketing';

C. 使用 OPENDATASOURCE 函數更新遠端資料表中的資料

下列範例會藉由指定 OPENDATASOURCE 資料列集函數將資料列插入遠端資料表。請使用 server_name 或 server_name\instance_name 格式，為資料來源指定有效的伺服器名稱。您可能必須針對特定分散式查詢設定 SQL Server 的執行個體。如需詳細資訊，請參閱＜特定分散式查詢選項＞。

UPDATE OPENQUERY (MyLinkServer, 'SELECT GroupName FROM HumanResources.Department WHERE DepartmentID = 4') 
SET GroupName = 'Sales and Marketing';

更新大型物件資料類型

本節的範例會示範如何更新使用大型物件 (LOB) 資料類型所定義之資料行中的值。

A. 搭配 .WRITE 使用 UPDATE 來修改 nvarchar(max) 資料行中的資料

下列範例會利用 .WRITE 子句來更新 DocumentSummary (Production.Document 資料表中的 nvarchar(max) 資料行) 中的部分值。components 一字會藉由指定取代文字、現有資料中要取代之字的起始位置 (位移)，以及要取代的字元數 (長度) 來取代為 features 一字。另外，這個範例也利用 OUTPUT 子句，將 DocumentSummary 資料行的前後影像傳回給 @MyTableVar 資料表變數。

USE AdventureWorks;
GO
DECLARE @MyTableVar table (
    DocumentID int NOT NULL,
    SummaryBefore nvarchar(max),
    SummaryAfter nvarchar(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT inserted.DocumentID,
       deleted.DocumentSummary, 
       inserted.DocumentSummary 
    INTO @MyTableVar
WHERE DocumentID = 3 ;
SELECT DocumentID, SummaryBefore, SummaryAfter 
FROM @MyTableVar;
GO

B. 搭配 .WRITE 使用 UPDATE 來新增和移除 nvarchar(max) 資料行中的資料

下列範例會新增和移除目前其值設為 NULL 之 nvarchar(max) 資料行中的資料。由於無法利用 .WRITE 子句來修改 NULL 資料行，因此，會先用暫用資料來擴展資料行。之後，便利用 .WRITE 子句，以正確的資料取代這項資料。其他範例會將資料附加至資料行值的結尾，移除 (截斷) 資料行中的資料，最後會從資料行中移除部分資料。SELECT 陳述式會顯示每個 UPDATE 陳述式所產生的資料修改。

USE AdventureWorks;
GO
-- Replacing NULL value with temporary data.
UPDATE Production.Document
SET DocumentSummary = N'Replacing NULL value'
WHERE DocumentID = 1;
GO

SELECT DocumentSummary 
FROM Production.Document
WHERE DocumentID = 1;
GO
-- Replacing temporary data with the correct data. Setting @Length to NULL 
-- truncates all existing data from the @Offset position.
UPDATE Production.Document
SET DocumentSummary .WRITE(N'Carefully inspect and maintain the tires and crank arms.',0,NULL)
WHERE DocumentID = 1;
GO

SELECT DocumentSummary 
FROM Production.Document
WHERE DocumentID = 1;
GO

-- Appending additional data to the end of the column by setting 
-- @Offset to NULL.
UPDATE Production.Document
SET DocumentSummary .WRITE (N' Appending data to the end of the column.', NULL, 0)
WHERE DocumentID = 1;
GO

SELECT DocumentSummary 
FROM Production.Document
WHERE DocumentID = 1;
GO

-- Removing all data from @Offset to the end of the existing value by 
-- setting expression to NULL. 
UPDATE Production.Document
SET DocumentSummary .WRITE (NULL, 56, 0)
WHERE DocumentID = 1;
GO

SELECT DocumentSummary 
FROM Production.Document
WHERE DocumentID = 1;
GO

-- Removing partial data beginning at position 9 and ending at 
-- position 21.
UPDATE Production.Document
SET DocumentSummary .WRITE ('',9, 12)
WHERE DocumentID = 1;
GO

SELECT DocumentSummary 
FROM Production.Document
WHERE DocumentID = 1;
GO

C. 搭配 OPENROWSET 使用 UPDATE 來修改 varbinary(max) 資料行

下列範例會利用新影像來取代 varbinary(max) 資料行所儲存的現有影像。OPENROWSET 函數會搭配 BULK 選項使用，將影像載入資料行中。這個範例假設指定的檔案路徑中，有名稱為 Tires.jpg 的檔案。

USE AdventureWorks;
GO
UPDATE Production.ProductPhoto
SET ThumbNailPhoto = (
    SELECT *
    FROM OPENROWSET(BULK 'c:\Tires.jpg', SINGLE_BLOB) AS x )
WHERE ProductPhotoID = 1;
GO

D. 使用 UPDATE 來修改 FILESTREAM 資料

下列範例會使用 UPDATE 陳述式來修改檔案系統檔案中的資料。但是，我們不建議您使用這個方法，將大量資料串流處理成檔案。請使用適當的 Win32 介面。下列範例會以文字 Xray 1 來取代檔案記錄中的所有文字。如需詳細資訊，請參閱＜FILESTREAM 概觀＞。

UPDATE Archive.dbo.Records
SET [Chart] = CAST('Xray 1' as varbinary(max))
WHERE [SerialNumber] = 2;

更新使用者定義型別

下列範例會修改 CLR 使用者定義型別 (UDT) 資料行中的值。下面將示範三種方法。如需有關使用者定義資料行的詳細資訊，請參閱＜CLR 使用者定義型別＞。

A. 使用系統資料類型

只要使用者定義型別支援從這個類型進行隱含或明確的轉換，您就可以在 SQL Server 系統資料類型中提供一個值，藉以更新 UDT。下列範例會顯示如何從字串進行明確的轉換，以便在使用者定義型別 Point 的資料行中更新值。

UPDATE dbo.Cities
SET Location = CONVERT(Point, '12.3:46.2')
WHERE Name = 'Anchorage';

B. 叫用方法

您可以叫用使用者定義型別的方法 (標示為 mutator) 來執行更新，藉以更新 UDT。下列範例會叫用名稱為 SetXY 的 Point 類型之 mutator 方法。這會更新此類型執行個體的狀態。

UPDATE dbo.Cities
SET Location.SetXY(23.5, 23.5)
WHERE Name = 'Anchorage';

C. 修改屬性或資料成員的值

您可以修改使用者定義型別的已註冊屬性值或公用資料成員值，藉以更新 UDT。提供值的運算式必須可隱含地轉換成屬性的類型。下列範例會修改使用者定義型別 Point 的 X 屬性值。

UPDATE dbo.Cities
SET Location.X = 23.5
WHERE Name = 'Anchorage';

使用提示來覆寫查詢最佳化工具的預設行為

本節的範例示範如何使用資料表和查詢提示，在處理 UPDATE 陳述式時暫時覆寫查詢最佳化工具的預設行為。

警告
由於 SQL Server 查詢最佳化工具通常會選取最好的查詢執行計畫，因此，我們建議資深的開發人員和資料庫管理員將提示當做最後的解決辦法。

A. 指定資料表提示

下列範例會指定資料表提示 TABLOCK。這個提示會指定在 Production.Product 資料表上採用共用鎖定，並且將鎖定保留到 UPDATE 陳述式結束為止。

USE AdventureWorks;
GO
UPDATE Production.Product
WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';
GO

B. 指定查詢提示

下列範例會在 UPDATE 陳述式中指定查詢提示OPTIMIZE FOR (@variable)。這個提示會指示查詢最佳化工具在查詢進行編譯和最佳化時，使用特定的區域變數值。只有在查詢最佳化期間，才使用這個值，在查詢執行期間，不使用這個值。

USE AdventureWorks;
GO
CREATE PROCEDURE Production.uspProductUpdate
@Product nvarchar(25)
AS
SET NOCOUNT ON;
UPDATE Production.Product
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE @Product
OPTION (OPTIMIZE FOR (@Product = 'BK-%') );
GO
-- Execute the stored procedure 
EXEC Production.uspProductUpdate 'BK-%';

擷取 UPDATE 陳述式的結果

本節的範例示範如何使用 OUTPUT 子句傳回 UPDATE 陳述式所影響之每個資料列的資訊，或是以該資料列為根據的運算式。這些結果可以傳回給負責處理的應用程式，以便用在確認訊息、封存或其他這類應用程式需求等用途上。

A. 搭配 OUTPUT 子句使用 UPDATE

下列範例會將 Employee 資料表前 10 個資料列的 VacationHours 資料行更新 25%，同時將 ModifiedDate 資料行中的值設定為目前的日期。OUTPUT 子句會將在 deleted.VacationHours 資料行中套用 UPDATE 陳述式之前便已存在的 VacationHours 值，以及 inserted.VacationHours 資料行中更新的值傳回給 @MyTableVar 資料表變數。

之後的兩個 SELECT 陳述式會傳回 @MyTableVar 中的值，以及 Employee 資料表中更新作業的結果。如需有關使用 OUTPUT 子句的更多範例，請參閱＜OUTPUT 子句 (Transact-SQL)＞。

USE AdventureWorks;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25,
    ModifiedDate = GETDATE() 
OUTPUT inserted.EmployeeID,
       deleted.VacationHours,
       inserted.VacationHours,
       inserted.ModifiedDate
INTO @MyTableVar;

--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO

--Display the result set of the table.
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

在其他陳述式中使用 UPDATE

本節中的範例示範如何在其他陳述式中使用 UPDATE。

A. 在預存程序中使用 UPDATE

下列範例會在預存程序中使用 UPDATE 陳述式。此程序會採用一個輸入參數 @NewHours 以及一個輸出參數 @RowCount。UPDATE 陳述式會使用 @NewHours 參數值來更新 HumanResources.Employee 資料表中的 VacationHours 資料行。@RowCount 輸出參數是用來將受影響的資料列數目傳回給區域變數。SET 子句中會使用 CASE 運算式，以條件方式判斷針對 VacationHours 所設定的值。按照時數支付薪資給員工時 (SalariedFlag = 0)，VacationHours 會設定為目前的時數加上 @NewHours 中指定的值，否則 VacationHours 會設定為 @NewHours 中指定的值。

USE AdventureWorks;
GO
CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours smallint,
@RowCount int OUTPUT
AS 
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
         ELSE @NewHours
       END
    )
WHERE CurrentFlag = 1;
SET @RowCount = @@ROWCOUNT;
GO

-- Execute the stored procedure and return the number of rows updated to the variable @RowCount
DECLARE @RowCount int;
EXECUTE HumanResources.Update_VacationHours 40, @RowCount OUTPUT;
SELECT @RowCount AS RowCount;

B. 在 TRY…CATCH 區塊中使用 UPDATE

下列範例會在 TRY…CATCH 區塊中使用 UPDATE 陳述式，以便處理在更新作業期間可能發生的執行錯誤。如需詳細資訊，請參閱＜在 Transact-SQL 中使用 TRY...CATCH＞。

USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
    -- Intentionally generate a constraint violation error.
    UPDATE HumanResources.Department
    SET Name = N'MyNewName'
    WHERE DepartmentID BETWEEN 1 AND 2;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

請參閱

參考

DELETE (Transact-SQL)

INSERT (Transact-SQL)

MERGE (Transact-SQL)

OPENROWSET (Transact-SQL)

WITH common_table_expression (Transact-SQL)

變更記錄

更新的內容
將 table_alias 加入至語法，做為指定要更新之目標資料表的方法。
在「限制事項」中新增有關使用通用資料表運算式做為 UPDATE 陳述式目標的資訊。