Implementazione di UPDATE con FROM o sottoqueryImplementing UPDATE with FROM or Subqueries

QUESTO ARGOMENTO SI APPLICA A: SìSQL ServerSìDatabase SQL di AzurenonAzure SQL Data Warehouse non Parallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

I moduli T-SQL compilati in modo nativo non supportano la clausola FROM e le sottoquery nelle istruzioni UPDATE (supportate in SELECT).Natively compiled T-SQL modules do not support the FROM clause and do not support subqueries in UPDATE statements (they are supported in SELECT). Le istruzioni UPDATE con la clausola FROM vengono in genere 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.UPDATE statements with FROM clause are typically used to update information in a table based on a table-valued parameter (TVP), or to update columns in a table in an AFTER trigger.

Per lo scenario di aggiornamento basato su un parametro con valori di tabella (TVP), vedere Implementazione della funzionalità MERGE.For the scenario of update based on a TVP, see Implementing MERGE Functionality in a Natively Compiled Stored Procedure.

L'esempio riportato di seguito illustra un aggiornamento eseguito in un trigger: la colonna LastUpdated della tabella viene aggiornata alla data/ora corrente dopo (AFTER) gli aggiornamenti.The below sample illustrates an update performed in a trigger - the LastUpdated column of the table is updated to the current date/time AFTER updates. La soluzione usa una variabile di tabella con una colonna identity e un ciclo WHILE per l'iterazione delle righe nella variabile di tabella e l'esecuzione dei singoli aggiornamenti.The workaround uses a table variable with an identity column, and a WHILE loop to iterate of the rows in the table variable and perform individual updates.

L'istruzione UPDATE T-SQL originale è la seguente:Here is the original T-SQL UPDATE statement :

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

Il codice T-SQL di esempio di questa sezione illustra una soluzione in grado di offrire buone prestazioni.The sample T-SQL code in this section demonstrates a workaround that provides good performance. La soluzione viene implementata in un trigger compilato in modo nativo.The workaround is implemented in a natively compiled trigger. È fondamentale notare nel codice:Crucial to notice in the code are:

  • Il tipo denominato dbo.Type1, ovvero un tipo di tabella ottimizzata per la memoria.The type named dbo.Type1, which is a memory-optimized table type.
  • Il ciclo WHILE nel trigger.The WHILE loop in the trigger.
    • Il ciclo recupera una riga alla volta da Inserted.The loop retrieves the rows from Inserted one at a time.
   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  
   -----------------------------  

   /**** Actual output:  

   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  
   ****/