以 FROM 或子查詢實作 UPDATE

發行項
01/02/2025

適用於：SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體

在 Transact-SQL UPDATE 陳述式中，於原生編譯的 T-SQL 模組內，「不」支援下列語法元素：

FROM 子句
子查詢

相較之下，SELECT 陳述式上的原生編譯模組中則「支援」上述元素。

搭配 FROM 子句的 UPDATE 陳述式常會用來根據資料表值參數 (TVP) 更新資料表中資訊，或在 AFTER 觸發程序中更新資料表中的資料行。

如需根據 TVP 的更新案例，請參閱在原生編譯的預存程序中實作 MERGE 功能。

下列範例會示範在觸發程序中執行更新。在資料表中，名為 LastUpdated 的資料行會設為「更新後」目前日期及時間。因應措施會透過使用下列項目來執行個別更新：

擁有 IDENTITY 資料行的資料表變數。
WHILE 迴圈，以逐一查看資料表變數中的資料列。

以下是原始的 T-SQL UPDATE 陳述式：

 UPDATE dbo.Table1  
     SET LastUpdated = SysDateTime()  
     FROM  
         dbo.Table1 t  
         JOIN Inserted i ON t.Id = i.Id;

下列區塊中範例 T-SQL 程式碼會示範提供良好效能的因應措施。此因應措施是在原生編譯的觸發程序中實作。請注意，此程式碼必須包含：

名為 dbo.Type1 的類型，也就是記憶體最佳化資料表類型。
觸發程序中的 WHILE 迴圈。
- 此迴圈會從 Inserted 一次擷取一個資料列。

DROP TABLE IF EXISTS dbo.Table1;  
GO

DROP TYPE IF EXISTS dbo.Type1;  
GO

-----------------------------
-- Table and table type.
-----------------------------
CREATE TABLE dbo.Table1 (
   Id INT NOT NULL PRIMARY KEY NONCLUSTERED,
   Column2 INT NOT NULL,
   LastUpdated DATETIME2 NOT NULL DEFAULT(SYSDATETIME())
)
WITH (MEMORY_OPTIMIZED = ON);
GO

CREATE TYPE dbo.Type1 AS TABLE (
   Id INT NOT NULL,
   RowID INT NOT NULL IDENTITY,
   INDEX ix_RowID HASH (RowID) WITH (BUCKET_COUNT = 1024)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

----------------------------------------
-- Trigger that contains the workaround
-- for UPDATE with FROM.
----------------------------------------
CREATE TRIGGER dbo.tr_a_u_Table1 ON dbo.Table1
   WITH NATIVE_COMPILATION, SCHEMABINDING
   AFTER UPDATE
AS
BEGIN
   ATOMIC
   WITH (
       TRANSACTION ISOLATION LEVEL = SNAPSHOT,
       LANGUAGE = N'us_english'
   )

   DECLARE @tabvar1 dbo.Type1;

   INSERT @tabvar1 (Id)
   SELECT Id
   FROM Inserted;

   DECLARE @i INT = 1,
       @Id INT,
       @max INT = SCOPE_IDENTITY();

   ---- Loop as a workaround to simulate a cursor.
   ---- Iterate over the rows in the memory-optimized table  
   ----   variable and perform an update for each row.  
   WHILE @i <= @max
   BEGIN
       SELECT @Id = Id
       FROM @tabvar1
       WHERE RowID = @i;

       UPDATE dbo.Table1
       SET LastUpdated = SysDateTime()
       WHERE Id = @Id;

       SET @i += 1;
   END
END
GO

---------------------------------
-- Test to verify functionality.
---------------------------------
SET NOCOUNT ON;

INSERT dbo.Table1 (Id, Column2)
VALUES (1, 9), (2, 9), (3, 600);

SELECT N'BEFORE-Update' AS [BEFORE-Update], *
FROM dbo.Table1
ORDER BY Id;

WAITFOR DELAY '00:00:01';

UPDATE dbo.Table1
SET Column2 += 1
WHERE Column2 <= 99;

SELECT N'AFTER--Update' AS [AFTER--Update], *
FROM dbo.Table1
ORDER BY Id;
GO

結果集如下所示。

BEFORE-Update   Id   Column2   LastUpdated  
BEFORE-Update   1       9      2016-04-20 21:18:42.8394659  
BEFORE-Update   2       9      2016-04-20 21:18:42.8394659  
BEFORE-Update   3     600      2016-04-20 21:18:42.8394659  

AFTER--Update   Id   Column2   LastUpdated  
AFTER--Update   1      10      2016-04-20 21:18:43.8529692  
AFTER--Update   2      10      2016-04-20 21:18:43.8529692  
AFTER--Update   3     600      2016-04-20 21:18:42.8394659

共用方式為

以 FROM 或子查詢實作 UPDATE

意見反應

其他資源