Implementazione di UPDATE con FROM o sottoquery

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure

In un modulo T-SQL compilato in modo nativo gli elementi della sintassi seguente dell'istruzione UPDATE di Transact-SQL non sono supportati:

• Clausola FROM
• Subqueries (Sottoquery)

Al contrario, in moduli compilati in modo nativo gli elementi precedenti sono supportati nell'istruzione SELECT.

Le istruzioni UPDATE con una clausola FROM vengono spesso usate per aggiornare le informazioni di una tabella in base a un parametro con valori di tabella (TVP) o per aggiornare le colonne di una tabella in un trigger AFTER.

Per lo scenario di aggiornamento basato su un parametro con valori di tabella (TVP), vedere Implementazione della funzionalità MERGE.

Nell'esempio seguente viene illustrato un aggiornamento eseguito in un trigger. Nella tabella la colonna denominata LastUpdated è impostata sugli aggiornamenti AFTER di data e ora correnti. La soluzione alternativa consente di eseguire i singoli aggiornamenti usando gli elementi seguenti:

• Una variabile di tabella che contiene una colonna IDENTITY.
• Un ciclo WHILE per eseguire l'iterazione delle righe nella variabile di tabella.

Ecco l'istruzione UPDATE originale di T-SQL:

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

Il codice T-SQL di esempio nel blocco seguente illustra una soluzione in grado di offrire prestazioni adeguate. La soluzione viene implementata in un trigger compilato in modo nativo. È fondamentale notare nel codice:

• Il tipo denominato dbo.Type1, ovvero un tipo di tabella ottimizzata per la memoria.
• Il ciclo WHILE nel trigger.
  • Il ciclo recupera una riga alla volta da 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

Il set di risultati è il seguente.

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