Implementación UPDATE con FROM o Subconsultas

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

En la instrucción Transact-SQL UPDATE , en un módulo T-SQL compilado de forma nativa, no se admiten los siguientes elementos de sintaxis:

  • La cláusula FROM
  • Subconsultas

En cambio, los elementos anteriores se admiten en módulos compilados de forma nativa en la instrucción SELECT.

UPDATE Las instrucciones con cláusula FROM se usan a menudo para actualizar información de una tabla a partir de un parámetro con valores de tabla (TVP), o para actualizar columnas de una tabla en un desencadenador de tipo AFTER.

Para ver el escenario de actualización basado en un TVP, consulte Implementación MERGE de la funcionalidad en un procedimiento almacenado compilado de forma nativa.

El ejemplo siguiente muestra una actualización realizada en un desencadenador. En la tabla, la columna denominada LastUpdated se establece en la fecha y hora actuales después de las actualizaciones. La solución realiza las actualizaciones individuales mediante el uso de los siguientes elementos:

  • Variable de tabla que tiene una IDENTITY columna.
  • Un bucle WHILE para iterar las filas de la variable de tabla.

Esta es la instrucción T-SQL UPDATE original:

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

El código de T-SQL de ejemplo del bloque siguiente muestra una solución alternativa que proporciona un buen rendimiento. Dicha solución se implementa en un desencadenador compilado de forma nativa. Es fundamental que se fije en lo siguiente en el código:

  • El tipo denominado dbo.Type1, que es un tipo de tabla optimizada para memoria.
  • El bucle WHILE en el disparador.
    • El bucle recupera las filas de Inserted una de cada vez.
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

Este es el conjunto de resultados.

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
  • Last updated on