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

QUESTO ARGOMENTO SI APPLICA A:sìSQL Server (a partire dalla versione 2014)sìDatabase SQL di AzurenoAzure SQL Data Warehouse noParallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2014)yesAzure 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 con ottimizzazione 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.
<span data-ttu-id="f7350-115">DROP TABLE IF EXISTS dbo.Table1;</span><span class="sxs-lookup"><span data-stu-id="f7350-115">DROP TABLE IF EXISTS dbo.Table1;</span></span>  
<span data-ttu-id="f7350-116">go</span><span class="sxs-lookup"><span data-stu-id="f7350-116">go</span></span>  
<span data-ttu-id="f7350-117">DROP TYPE IF EXISTS dbo.Type1;</span><span class="sxs-lookup"><span data-stu-id="f7350-117">DROP TYPE IF EXISTS dbo.Type1;</span></span>  
<span data-ttu-id="f7350-118">go</span><span class="sxs-lookup"><span data-stu-id="f7350-118">go</span></span>  
-----------------------------  
<a name="---table-and-table-type"></a><span data-ttu-id="f7350-119">-- Table and table type</span><span class="sxs-lookup"><span data-stu-id="f7350-119">-- Table and table type</span></span>
-----------------------------

<span data-ttu-id="f7350-120">CREATE TABLE dbo.Table1</span><span class="sxs-lookup"><span data-stu-id="f7350-120">CREATE TABLE dbo.Table1</span></span>  
<span data-ttu-id="f7350-121">(</span><span class="sxs-lookup"><span data-stu-id="f7350-121">(</span></span>  
    <span data-ttu-id="f7350-122">Id           INT        NOT NULL  PRIMARY KEY NONCLUSTERED,</span><span class="sxs-lookup"><span data-stu-id="f7350-122">Id           INT        NOT NULL  PRIMARY KEY NONCLUSTERED,</span></span>  
    <span data-ttu-id="f7350-123">Column2      INT        NOT NULL,</span><span class="sxs-lookup"><span data-stu-id="f7350-123">Column2      INT        NOT NULL,</span></span>  
    <span data-ttu-id="f7350-124">LastUpdated  DATETIME2  NOT NULL  DEFAULT (SYSDATETIME())</span><span class="sxs-lookup"><span data-stu-id="f7350-124">LastUpdated  DATETIME2  NOT NULL  DEFAULT (SYSDATETIME())</span></span>  
<span data-ttu-id="f7350-125">)</span><span class="sxs-lookup"><span data-stu-id="f7350-125">)</span></span>  
    <span data-ttu-id="f7350-126">WITH (MEMORY_OPTIMIZED = ON);</span><span class="sxs-lookup"><span data-stu-id="f7350-126">WITH (MEMORY_OPTIMIZED = ON);</span></span>  
<span data-ttu-id="f7350-127">go</span><span class="sxs-lookup"><span data-stu-id="f7350-127">go</span></span>  


<span data-ttu-id="f7350-128">CREATE TYPE dbo.Type1 AS TABLE</span><span class="sxs-lookup"><span data-stu-id="f7350-128">CREATE TYPE dbo.Type1 AS TABLE</span></span>  
<span data-ttu-id="f7350-129">(</span><span class="sxs-lookup"><span data-stu-id="f7350-129">(</span></span>  
    <span data-ttu-id="f7350-130">Id       INT NOT  NULL,</span><span class="sxs-lookup"><span data-stu-id="f7350-130">Id       INT NOT  NULL,</span></span>  

    RowID    INT NOT  NULL  IDENTITY,  
    INDEX ix_RowID HASH (RowID) WITH (BUCKET_COUNT=1024)
<span data-ttu-id="f7350-131">)</span><span class="sxs-lookup"><span data-stu-id="f7350-131">)</span></span>   
    <span data-ttu-id="f7350-132">WITH (MEMORY_OPTIMIZED = ON);</span><span class="sxs-lookup"><span data-stu-id="f7350-132">WITH (MEMORY_OPTIMIZED = ON);</span></span>  
<span data-ttu-id="f7350-133">go</span><span class="sxs-lookup"><span data-stu-id="f7350-133">go</span></span>  
----------------------------- 
<a name="---trigger-that-contains-the-workaround-for-update-with-from"></a><span data-ttu-id="f7350-134">-- trigger that contains the workaround for UPDATE with FROM</span><span class="sxs-lookup"><span data-stu-id="f7350-134">-- trigger that contains the workaround for UPDATE with FROM</span></span> 
-----------------------------  

<span data-ttu-id="f7350-135">CREATE TRIGGER dbo.tr_a_u_Table1</span><span class="sxs-lookup"><span data-stu-id="f7350-135">CREATE TRIGGER dbo.tr_a_u_Table1</span></span>  
    <span data-ttu-id="f7350-136">ON dbo.Table1</span><span class="sxs-lookup"><span data-stu-id="f7350-136">ON dbo.Table1</span></span>  
    <span data-ttu-id="f7350-137">WITH NATIVE_COMPILATION, SCHEMABINDING</span><span class="sxs-lookup"><span data-stu-id="f7350-137">WITH NATIVE_COMPILATION, SCHEMABINDING</span></span>  
    <span data-ttu-id="f7350-138">AFTER UPDATE</span><span class="sxs-lookup"><span data-stu-id="f7350-138">AFTER UPDATE</span></span>  
<span data-ttu-id="f7350-139">AS BEGIN ATOMIC WITH</span><span class="sxs-lookup"><span data-stu-id="f7350-139">AS BEGIN ATOMIC WITH</span></span>  
    <span data-ttu-id="f7350-140">(</span><span class="sxs-lookup"><span data-stu-id="f7350-140">(</span></span>  
    <span data-ttu-id="f7350-141">TRANSACTION ISOLATION LEVEL = SNAPSHOT,</span><span class="sxs-lookup"><span data-stu-id="f7350-141">TRANSACTION ISOLATION LEVEL = SNAPSHOT,</span></span>  
    <span data-ttu-id="f7350-142">LANGUAGE = N'us_english'</span><span class="sxs-lookup"><span data-stu-id="f7350-142">LANGUAGE = N'us_english'</span></span>  
    <span data-ttu-id="f7350-143">)</span><span class="sxs-lookup"><span data-stu-id="f7350-143">)</span></span>  

  <span data-ttu-id="f7350-144">DECLARE @tabvar1 dbo.Type1;</span><span class="sxs-lookup"><span data-stu-id="f7350-144">DECLARE @tabvar1 dbo.Type1;</span></span>  

  <span data-ttu-id="f7350-145">INSERT @tabvar1 (Id)</span><span class="sxs-lookup"><span data-stu-id="f7350-145">INSERT @tabvar1 (Id)</span></span>   
      <span data-ttu-id="f7350-146">SELECT Id FROM Inserted;</span><span class="sxs-lookup"><span data-stu-id="f7350-146">SELECT Id FROM Inserted;</span></span>  

  <span data-ttu-id="f7350-147">DECLARE</span><span class="sxs-lookup"><span data-stu-id="f7350-147">DECLARE</span></span>  
      <span data-ttu-id="f7350-148">@i INT = 1,  @Id INT,</span><span class="sxs-lookup"><span data-stu-id="f7350-148">@i INT = 1,  @Id INT,</span></span>  
      <span data-ttu-id="f7350-149">@max INT = SCOPE_IDENTITY();</span><span class="sxs-lookup"><span data-stu-id="f7350-149">@max INT = SCOPE_IDENTITY();</span></span>  

  <span data-ttu-id="f7350-150">---- Loop as a workaround to simulate a cursor.</span><span class="sxs-lookup"><span data-stu-id="f7350-150">---- Loop as a workaround to simulate a cursor.</span></span>
<span data-ttu-id="f7350-151">---- Iterate over the rows in the memory-optimized table</span><span class="sxs-lookup"><span data-stu-id="f7350-151">---- Iterate over the rows in the memory-optimized table</span></span>  
  <span data-ttu-id="f7350-152">----   variable and perform an update for each row.</span><span class="sxs-lookup"><span data-stu-id="f7350-152">----   variable and perform an update for each row.</span></span>  

  <span data-ttu-id="f7350-153">WHILE @i <= @max</span><span class="sxs-lookup"><span data-stu-id="f7350-153">WHILE @i <= @max</span></span>  
  <span data-ttu-id="f7350-154">BEGIN</span><span class="sxs-lookup"><span data-stu-id="f7350-154">BEGIN</span></span>  
      <span data-ttu-id="f7350-155">SELECT @Id = Id</span><span class="sxs-lookup"><span data-stu-id="f7350-155">SELECT @Id = Id</span></span>  
          <span data-ttu-id="f7350-156">FROM @tabvar1</span><span class="sxs-lookup"><span data-stu-id="f7350-156">FROM @tabvar1</span></span>  
          <span data-ttu-id="f7350-157">WHERE RowID = @i;</span><span class="sxs-lookup"><span data-stu-id="f7350-157">WHERE RowID = @i;</span></span>  

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

      SET @i += 1;  
  <span data-ttu-id="f7350-158">END</span><span class="sxs-lookup"><span data-stu-id="f7350-158">END</span></span>  
<span data-ttu-id="f7350-159">END</span><span class="sxs-lookup"><span data-stu-id="f7350-159">END</span></span>  
<span data-ttu-id="f7350-160">go</span><span class="sxs-lookup"><span data-stu-id="f7350-160">go</span></span>  
-----------------------------  
<a name="---test-to-verify-functionality"></a><span data-ttu-id="f7350-161">-- Test to verify functionality</span><span class="sxs-lookup"><span data-stu-id="f7350-161">-- Test to verify functionality</span></span>
-----------------------------  

<span data-ttu-id="f7350-162">SET NOCOUNT ON;</span><span class="sxs-lookup"><span data-stu-id="f7350-162">SET NOCOUNT ON;</span></span>  

<span data-ttu-id="f7350-163">INSERT dbo.Table1 (Id, Column2)</span><span class="sxs-lookup"><span data-stu-id="f7350-163">INSERT dbo.Table1 (Id, Column2)</span></span>  
    <span data-ttu-id="f7350-164">VALUES (1,9), (2,9), (3,600);</span><span class="sxs-lookup"><span data-stu-id="f7350-164">VALUES (1,9), (2,9), (3,600);</span></span>  

<span data-ttu-id="f7350-165">SELECT N'BEFORE-Update' AS [BEFORE-Update], *</span><span class="sxs-lookup"><span data-stu-id="f7350-165">SELECT N'BEFORE-Update' AS [BEFORE-Update], *</span></span>  
    <span data-ttu-id="f7350-166">FROM dbo.Table1</span><span class="sxs-lookup"><span data-stu-id="f7350-166">FROM dbo.Table1</span></span>  
    <span data-ttu-id="f7350-167">ORDER BY Id;</span><span class="sxs-lookup"><span data-stu-id="f7350-167">ORDER BY Id;</span></span>  

<span data-ttu-id="f7350-168">WAITFOR DELAY '00:00:01';</span><span class="sxs-lookup"><span data-stu-id="f7350-168">WAITFOR DELAY '00:00:01';</span></span>  

<span data-ttu-id="f7350-169">UPDATE dbo.Table1</span><span class="sxs-lookup"><span data-stu-id="f7350-169">UPDATE dbo.Table1</span></span>  
    <span data-ttu-id="f7350-170">SET   Column2 += 1</span><span class="sxs-lookup"><span data-stu-id="f7350-170">SET   Column2 += 1</span></span>  
    <span data-ttu-id="f7350-171">WHERE Column2 <= 99;</span><span class="sxs-lookup"><span data-stu-id="f7350-171">WHERE Column2 <= 99;</span></span>  

<span data-ttu-id="f7350-172">SELECT N'AFTER--Update' AS [AFTER--Update], *</span><span class="sxs-lookup"><span data-stu-id="f7350-172">SELECT N'AFTER--Update' AS [AFTER--Update], *</span></span>  
    <span data-ttu-id="f7350-173">FROM dbo.Table1</span><span class="sxs-lookup"><span data-stu-id="f7350-173">FROM dbo.Table1</span></span>  
    <span data-ttu-id="f7350-174">ORDER BY Id;</span><span class="sxs-lookup"><span data-stu-id="f7350-174">ORDER BY Id;</span></span>  
<span data-ttu-id="f7350-175">go</span><span class="sxs-lookup"><span data-stu-id="f7350-175">go</span></span>  
-----------------------------  

<span data-ttu-id="f7350-176">/**** Actual output:</span><span class="sxs-lookup"><span data-stu-id="f7350-176">/**** Actual output:</span></span>  

<span data-ttu-id="f7350-177">BEFORE-Update   Id   Column2   LastUpdated</span><span class="sxs-lookup"><span data-stu-id="f7350-177">BEFORE-Update   Id   Column2   LastUpdated</span></span>  
<span data-ttu-id="f7350-178">BEFORE-Update   1       9      2016-04-20 21:18:42.8394659</span><span class="sxs-lookup"><span data-stu-id="f7350-178">BEFORE-Update   1       9      2016-04-20 21:18:42.8394659</span></span>  
<span data-ttu-id="f7350-179">BEFORE-Update   2       9      2016-04-20 21:18:42.8394659</span><span class="sxs-lookup"><span data-stu-id="f7350-179">BEFORE-Update   2       9      2016-04-20 21:18:42.8394659</span></span>  
<span data-ttu-id="f7350-180">BEFORE-Update   3     600      2016-04-20 21:18:42.8394659</span><span class="sxs-lookup"><span data-stu-id="f7350-180">BEFORE-Update   3     600      2016-04-20 21:18:42.8394659</span></span>  

<span data-ttu-id="f7350-181">AFTER--Update   Id   Column2   LastUpdated</span><span class="sxs-lookup"><span data-stu-id="f7350-181">AFTER--Update   Id   Column2   LastUpdated</span></span>  
<span data-ttu-id="f7350-182">AFTER--Update   1      10      2016-04-20 21:18:43.8529692</span><span class="sxs-lookup"><span data-stu-id="f7350-182">AFTER--Update   1      10      2016-04-20 21:18:43.8529692</span></span>  
<span data-ttu-id="f7350-183">AFTER--Update   2      10      2016-04-20 21:18:43.8529692</span><span class="sxs-lookup"><span data-stu-id="f7350-183">AFTER--Update   2      10      2016-04-20 21:18:43.8529692</span></span>  
<span data-ttu-id="f7350-184">AFTER--Update   3     600      2016-04-20 21:18:42.8394659</span><span class="sxs-lookup"><span data-stu-id="f7350-184">AFTER--Update   3     600      2016-04-20 21:18:42.8394659</span></span>  
****/