UPDATE (Transact-SQL)

Cambia los datos de una tabla o vista.

Convenciones de sintaxis de Transact-SQL

Sintaxis

[ WITH <common_table_expression> [...n] ]
UPDATE 
    [ TOP ( expression ) [ PERCENT ] ] 
    { <object> | rowset_function_limited 
     [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
    }
    SET 
        { column_name = { expression | DEFAULT | NULL }
          | { udt_column_name.{ { property_name = expression 
                                | field_name = expression } 
                               | method_name ( argument [ ,...n ] ) 
                              } 
            }
          | column_name { .WRITE ( expression , @Offset , @Length ) }
          | @variable = expression 
          | @variable = column = expression [ ,...n ] 
        } [ ,...n ] 
    [ <OUTPUT Clause> ]
    [ FROM{ <table_source> } [ ,...n ] ] 
    [ WHERE { <search_condition> 
            | { [ CURRENT OF 
                  { { [ GLOBAL ] cursor_name } 
                      | cursor_variable_name 
                  } 
                ]
              }
            } 
    ] 
    [ OPTION ( <query_hint> [ ,...n ] ) ]
[ ; ]

<object> ::=
{ 
    [ server_name . database_name . schema_name . 
    | database_name .[ schema_name ] . 
    | schema_name .
    ]
        table_or_view_name}

Argumentos

• WITH <common_table_expression>
  Especifica la vista o el conjunto de resultados temporal indicado, que también se conoce como expresión de tabla común (CTE), definido en el ámbito de la instrucción UPDATE. El conjunto de resultados CTE se deriva de una consulta simple. La instrucción UPDATE hace referencia al conjunto de resultados.

  Las expresiones de tabla comunes también se pueden utilizar con las instrucciones SELECT, INSERT, DELETE y CREATE VIEW. Para obtener más información, vea WITH common_table_expression (Transact-SQL).

• TOP ( expression**)** [ PERCENT ]
  Especifica el número o porcentaje de filas que se va a actualizar. expression puede ser un número o un porcentaje de filas.

  Las filas a las que se hace referencia en la expresión TOP utilizada con INSERT, UPDATE o DELETE no se ordenan.

  En las instrucciones INSERT, UPDATE y DELETE se deben utilizar paréntesis para delimitar expression en TOP. Para obtener más información, vea TOP (Transact-SQL).

• server_name
  Es el nombre del servidor (un nombre de servidor vinculado o la función OPENDATASOURCE como nombre de servidor) en el que se encuentra la tabla o la vista. Si se especifica server_name, son obligatorios database_name y schema_name.

• database_name
  Es el nombre de la base de datos.

• schema_name
  Es el nombre del esquema al que pertenece la tabla o vista.

• table_or view_name
  Es el nombre de la tabla o vista cuyas filas se deben actualizar.

  En este ámbito, se puede utilizar una variable table como origen de tabla en una instrucción UPDATE.

  La vista a la que hace referencia table_or_view_name debe poderse actualizar y debe hacer referencia exactamente a una tabla base en la cláusula FROM de la vista. Para obtener más información acerca de las vistas que se pueden actualizar, vea CREATE VIEW (Transact-SQL).

• rowset_function_limited
  Es la función OPENQUERY u OPENROWSET, dependiendo del proveedor. Para obtener más información acerca de la funcionalidad que necesita el proveedor, vea Requisitos de UPDATE y DELETE para los proveedores OLE DB.

• WITH ( <Table_Hint_Limited> )
  Especifica una o más sugerencias de tabla que se permiten para una tabla de destino. La palabra clave WITH y los paréntesis son obligatorios. No se permiten NOLOCK ni READUNCOMMITTED. Para obtener más información acerca de las sugerencias de tabla, vea Sugerencias de tabla (Transact-SQL).

• SET
  Especifica la lista de nombres de variable o de columna que se van a actualizar.

• column_name
  Es una columna que contiene los datos que se van a cambiar. column_name debe existir en table_or view_name. Las columnas de identidad no se pueden actualizar.

• expression
  Es una variable, un valor literal, una expresión o una instrucción de subselección entre paréntesis que devuelve un solo valor. El valor devuelto por expression sustituye al valor existente en column_name o @variable.

• DEFAULT
  Especifica que el valor predeterminado definido para la columna debe reemplazar al valor existente en esa columna. Esta operación también puede utilizarse para cambiar la columna a NULL si no tiene asignado ningún valor predeterminado y se ha definido para aceptar valores NULL.

• udt_column_name
  Es una columna de un tipo definido por el usuario.

• property_name | field_name
  Es un miembro de propiedad público o un miembro de datos público de un tipo definido por el usuario.

• method_name**(**argument [ ,... n] )
  Es un método mutador público no estático de udt_column_name que utiliza uno o varios argumentos.

• .WRITE (expression,@Offset,@Length**)**
  Especifica que una sección del valor de column_name se va a modificar. expression sustituye a las unidades @Length a partir de @Offset de column_name. Con esta cláusula sólo se pueden especificar columnas de tipo varchar(max), nvarchar(max) o varbinary(max). column_name no puede ser NULL y no se puede calificar con un nombre de tabla o alias de tabla.

  expression es el valor que se copia a column_name. expression debe evaluarse como el tipo de column_name o debe tener capacidad para convertirse implícitamente a ese tipo. Si expression se establece como NULL, se omitirá @Length, y se truncará el valor de column_name en el @Offset especificado.

  @Offset es el punto inicial del valor de column_name en el que se escribe expression. @Offset es una posición ordinal basada en cero, es bigint y no puede ser un número negativo. Si @Offset es NULL, la operación de actualización anexa expression al final del valor de column_name existente y @Length no se tiene en cuenta. Si @Offset es mayor que la longitud del valor de column_name, el SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) de Microsoft devuelve un error. Si la suma de @Offset y @Length excede el final del valor subyacente de la columna, se elimina todo hasta el último carácter del valor. Si la suma de @Offset y LEN(expression) es superior al tamaño subyacente declarado, se produce un error.

  @Length es la longitud de la sección de la columna, a partir de @Offset, que se reemplaza por expression. @Length es bigint y no puede ser un número negativo. Si @Length en NULL, la operación de actualización quita todos los datos de @Offset hasta el final del valor de column_name.

  Para obtener más información, vea la sección Notas.

• **@**variable
  Es una variable declarada que se establece en el valor devuelto por expression.

  SET **@**variable = column = expression establece la variable en el mismo valor que la columna. Esto no es lo mismo que SET **@**variable = column, column = expression, que establece la variable en el valor previo a la actualización de la columna.

• <OUTPUT_Clause>
  Devuelve datos actualizados o expresiones basadas en ellos como parte de la operación UPDATE. La cláusula OUTPUT no se admite en instrucciones DML dirigidas a tablas o vistas remotas. Para obtener más información, vea OUTPUT (cláusula de Transact-SQL).

• FROM <table_source>
  Especifica que se utiliza un origen de tabla, vista o tabla derivada para proporcionar los criterios de la operación de actualización. Para obtener más información, vea FROM (Transact-SQL).

  Si el objeto que se actualiza es el que se indica en la cláusula FROM y sólo hay una referencia al objeto en ella, puede especificarse o no un alias de objeto. Si el objeto que se actualiza aparece más de una vez en la cláusula FROM, una única referencia al objeto no debe especificar un alias de tabla. Todas las demás referencias al objeto de la cláusula FROM deben incluir un alias de objeto.

  Una vista con un desencadenador INSTEAD OF UPDATE no puede ser el destino de UPDATE con una cláusula FROM.

• WHERE
  Especifica las condiciones que limitan las filas que se actualizan. Hay dos modos de actualización, dependiendo del formato de cláusula WHERE que se utilice:

  • Las actualizaciones por búsqueda especifican una condición de búsqueda para calificar las filas que se van a eliminar.
  • Las actualizaciones posicionadas utilizan la cláusula CURRENT OF para especificar un cursor. La operación de actualización se produce en la posición actual del cursor.

• <search_condition>
  Especifica la condición que debe cumplirse para que se actualicen las filas. La condición de búsqueda también puede ser la condición en la que se basa una combinación. El número de predicados que pueden incluirse en una condición de búsqueda no tiene límite. Para obtener más información acerca de los predicados y las condiciones de búsqueda, vea Condiciones de búsqueda (Transact-SQL).

• CURRENT OF
  Indica que la actualización se realice en la posición actual del cursor especificado.

• GLOBAL
  Especifica que cursor_name hace referencia a un cursor global.

• cursor_name
  Es el nombre del cursor abierto desde el que se debe realizar la recuperación. Si hay un cursor global y otro local con el nombre cursor_name, este argumento hace referencia al cursor global si se especifica GLOBAL; de lo contrario, hace referencia al cursor local. El cursor debe permitir actualizaciones.

• cursor_variable_name
  Es el nombre de una variable de cursor. cursor_variable_name debe hacer referencia a un cursor que permita actualizaciones.

• OPTION ( <query_hint> [ ,... n ] )
  Especifica que se utilizan las sugerencias del optimizador para personalizar el modo en que el Database Engine (Motor de base de datos) procesa la instrucción. Para obtener más información, vea Query Hint (Transact-SQL).

Notas

La instrucción UPDATE se registra; sin embargo, las actualizaciones parciales de tipos de datos de valores grandes mediante la cláusula **.**WRITE se registran mínimamente. Para obtener más información, vea "Actualizar tipos de datos de valores grandes", a continuación.

Se pueden utilizar instrucciones UPDATE en el cuerpo de las funciones definidas por el usuario sólo si la tabla que se modifica es una variable table.

Si la actualización de una fila infringe una restricción o una regla, infringe la configuración de valores NULL de la columna o si el nuevo valor es de un tipo de datos incompatible, se cancela la instrucción, se devuelve un error y no se actualiza ningún registro.

Cuando una instrucción UPDATE encuentra un error aritmético (error de desbordamiento, división por cero o de dominio) durante la evaluación de la expresión, la actualización no se lleva a cabo. El resto del lote no se ejecuta y se devuelve un mensaje de error.

Si la actualización de una o varias columnas que participan en un índice agrupado hace que el tamaño del índice agrupado y de la fila supere 8.060 bytes, la actualización no se produce y se devuelve un mensaje de error.

Si la instrucción UPDATE pudo cambiar más de una fila durante la actualización de la clave de agrupación y una o varias columnas text, ntext o image, la actualización parcial de estas columnas se ejecuta como una sustitución completa de los valores.

Todas las columnas char y nchar se rellenan a la derecha hasta la longitud definida.

La configuración de la opción SET ROWCOUNT se omite para las instrucciones UPDATE de tablas remotas y vistas locales y remotas con particiones.

Si ANSI_PADDING se establece en OFF, se quitan todos los espacios finales de los datos insertados en las columnas varchar y nvarchar, excepto en las cadenas que contienen sólo espacios. Estas cadenas se truncan en una cadena vacía. Si ANSI_PADDING se establece en ON, se insertan espacios al final. El controlador ODBC de Microsoft SQL Server y el proveedor OLE DB para SQL Server establecen automáticamente SET ANSI_PADDING en ON para cada conexión. Se puede configurar en orígenes de datos ODBC o mediante atributos o propiedades de conexión. Para obtener más información, vea SET ANSI_PADDING (Transact-SQL).

Una actualización posicionada que utiliza una cláusula WHERE CURRENT OF actualiza la fila que se encuentra en la posición actual del cursor. Este método puede ser más preciso que una actualización por búsqueda que utilice una cláusula WHERE <search_condition> para calificar las filas que se deben actualizar. Una actualización por búsqueda modifica varias filas cuando la condición de búsqueda no identifica una sola fila de forma exclusiva.

Utilizar UPDATE con la cláusula FROM

Los resultados de una instrucción UPDATE están sin definir si la instrucción incluye una cláusula FROM que no se especifica de manera que sólo haya un valor disponible para cada caso de columna que se actualice, es decir, si la instrucción UPDATE no es determinista. Por ejemplo, en la instrucción UPDATE de la siguiente secuencia de comandos, las dos filas de Table1 cumplen los requisitos de la cláusula FROM de la instrucción UPDATE, pero no se define qué fila de Table1 se utiliza para actualizar la fila de Table2.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL
    DROP TABLE dbo.Table1;
GO
IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL
    DROP TABLE dbo.Table2;
GO
CREATE TABLE dbo.Table1 
    (ColA int NOT NULL, ColB decimal(10,3) NOT NULL);
GO
CREATE TABLE dbo.Table2 
    (ColA int PRIMARY KEY NOT NULL, ColB decimal(10,3) NOT NULL);
GO
INSERT INTO dbo.Table1 VALUES(1, 10.0);
INSERT INTO dbo.Table1 VALUES(1, 20.0);
INSERT INTO dbo.Table2 VALUES(1, 0.0);
GO
UPDATE dbo.Table2 
SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB
FROM dbo.Table2 
    INNER JOIN dbo.Table1 
    ON (dbo.Table2.ColA = dbo.Table1.ColA);
GO
SELECT ColA, ColB 
FROM dbo.Table2;

Puede ocurrir el mismo problema cuando se combinan las cláusulas FROM y WHERE CURRENT OF. En el ejemplo siguiente, las dos filas de Table2 cumplen los requisitos de la cláusula FROM de la instrucción UPDATE. No se ha definido qué fila de Table2 se utilizará para actualizar la fila de Table1.

USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.Table1', 'U') IS NOT NULL
    DROP TABLE dbo.Table1;
GO
IF OBJECT_ID ('dbo.Table2', 'U') IS NOT NULL
    DROP TABLE dbo.Table2;
GO
CREATE TABLE dbo.Table1
    (c1 int PRIMARY KEY NOT NULL, c2 int NOT NULL);
GO
CREATE TABLE dbo.Table2
    (d1 int PRIMARY KEY NOT NULL, d2 int NOT NULL);
GO
INSERT INTO dbo.Table1 VALUES (1, 10);
INSERT INTO dbo.Table2 VALUES (1, 20);
INSERT INTO dbo.Table2 VALUES (2, 30);
GO
DECLARE abc CURSOR LOCAL FOR
    SELECT c1, c2 
    FROM dbo.Table1;
OPEN abc;
FETCH abc;
UPDATE dbo.Table1 
SET c2 = c2 + d2 
FROM dbo.Table2 
WHERE CURRENT OF abc;
GO
SELECT c1, c2 FROM dbo.Table1;
GO

Actualizar columnas de tipos definidos por el usuario

Hay varios métodos para actualizar los valores de columnas de tipos definidos por el usuario:

• Suministrar un valor de un tipo de datos del sistema de SQL Server, siempre y cuando el tipo definido por el usuario admita la conversión implícita o explícita desde ese tipo. En el ejemplo siguiente se muestra cómo actualizar un valor de una columna de tipo Point, definido por el usuario, mediante la conversión explícita de una cadena.

  UPDATE Cities
  SET Location = CONVERT(Point, '12.3:46.2')
  WHERE Name = 'Anchorage';
  

• Invocar un método, marcado como mutator, del tipo definido por el usuario, para realizar la actualización. En el ejemplo siguiente se invoca un método mutador de tipo Point denominado SetXY. Esto actualiza el estado de la instancia del tipo.

  UPDATE Cities
  SET Location.SetXY(23.5, 23.5)
  WHERE Name = 'Anchorage';
  

  [!NOTA] SQL Server devuelve un error si se invoca un método mutador en un valor NULL de Transact-SQL, o si un nuevo valor producido por un método mutador es NULL.

• Modificar el valor de un miembro de propiedad registrado o un miembro de datos público del tipo definido por el usuario. La expresión que suministra el valor debe poder convertirse implícitamente al tipo de la propiedad. En el ejemplo siguiente se modifica el valor de la propiedad X del tipo Point definido por el usuario.

  UPDATE Cities
  SET Location.X = 23.5
  WHERE Name = 'Anchorage';
  

  Para modificar diferentes propiedades de la misma columna de tipo definido por el usuario, emita varias instrucciones UPDATE o invoque un método mutador del tipo.

Actualizar tipos de datos de valores grandes

Utilice la cláusula .WRITE (expression, @Offset**,**@Length) para realizar una actualización parcial o completa de los tipos de datos varchar(max), nvarchar(max) y varbinary(max). Por ejemplo, la actualización parcial de una columna varchar(max) podría eliminar o modificar sólo los 200 primeros caracteres de la columna, mientras que una actualización completa eliminaría o modificaría todos los datos de la columna. Las actualizaciones **.**WRITE que insertan o anexan datos nuevos se registran mínimamente si se ha establecido para la base de datos el modelo de recuperación por medio de registros de operaciones masivas o el modelo de recuperación simple. Tenga en cuenta que el registro mínimo no se utiliza cuando se actualizan valores existentes. Para obtener más información, vea Operaciones registradas mínimamente.

El SQL Server 2005 Database Engine (Motor de base de datos de SQL Server 2005) convierte una actualización parcial en actualización completa cuando la instrucción UPDATE realiza una de estas acciones:

• Cambia una columna de clave de la tabla o vista con particiones.
• Modifica más de una fila y también actualiza la clave de un índice agrupado no único en un valor no constante.

No se puede usar la cláusula **.**WRITE para actualizar una columna NULL o establecer el valor de column_name como NULL.

@Offset y @Length se especifican en bytes para los tipos de datos varbinary y varchar, y en caracteres para el tipo de datos nvarchar. Se calculan los desplazamientos correspondientes para las intercalaciones del juego de caracteres de doble byte (DBCS).

Para que el rendimiento sea óptimo, se recomienda insertar o actualizar los datos en tamaños de fragmento que sean múltiplos de 8.040 bytes.

Si en una cláusula OUTPUT se hace referencia a la columna modificada por la cláusula **.**WRITE, se devuelve el valor completo de la columna, bien la imagen anterior de **deleted.**column_name o bien la imagen posterior de **inserted.**column_name, en la columna especificada en la variable de tabla. Vea el ejemplo G a continuación.

Para obtener la misma funcionalidad de **.**WRITE con otros tipos de datos de carácter o binarios, utilice STUFF (Transact-SQL).

Actualizar columnas de tipo text, ntext e image

Al modificar una columna text, ntext o image con UPDATE, se inicializa la columna, se le asigna un puntero de texto válido y se le asigna al menos una página de datos, a menos que la columna se actualice con NULL.

Para reemplazar o modificar grandes bloques de datos text, ntext o image, utilice WRITETEXT o UPDATETEXT en lugar de la instrucción UPDATE.

Importante:
Los tipos de datos ntext, text e image se quitarán en una versión futura de Microsoft SQL Server. Evite su uso en nuevos trabajos de desarrollo y piense en modificar las aplicaciones que los usan actualmente. Utilice nvarchar(max), varchar(max) y varbinary(max) en su lugar. Para obtener más información, vea Usar tipos de datos de valores grandes.

Utilizar desencadenadores INSTEAD OF en acciones UPDATE

Cuando se define un desencadenador INSTEAD OF para las acciones UPDATE de una tabla, se ejecuta el desencadenador en lugar de la instrucción UPDATE. En versiones anteriores de SQL Server sólo se admite la definición de desencadenadores AFTER en instrucciones UPDATE y otras instrucciones de modificación de datos. No se puede especificar la cláusula FROM en una instrucción UPDATE que haga referencia, directa o indirectamente, a una vista que tiene definido un desencadenador INSTEAD OF. Para obtener más información acerca de los desencadenadores INSTEAD OF, vea CREATE TRIGGER (Transact-SQL).

Configurar variables y columnas

Es posible utilizar nombres de variables en las instrucciones UPDATE para mostrar los valores nuevos y antiguos afectados, pero sólo se recomienda cuando la instrucción UPDATE afecta a un único registro. Si la instrucción UPDATE afecta a varios registros, para devolver los valores nuevos y antiguos de cada registro, utilice la cláusula OUTPUT.

Permisos

Se requieren permisos UPDATE en la tabla de destino. También se requieren permisos SELECT para la tabla que se actualiza si la instrucción UPDATE contiene una cláusula WHERE o en el caso de que el argumento expression de la cláusula SET utilice una columna de la tabla.

Los permisos para utilizar UPDATE corresponden de forma predeterminada a los miembros de la función fija de servidor sysadmin, de las funciones fijas de base de datos db_owner y db_datawriter y al propietario de la tabla. Los miembros de las funciones sysadmin, db_owner y db_securityadmin y el propietario de la tabla pueden transferir permisos a otros usuarios.

Ejemplos

A. Utilizar una instrucción UPDATE simple

En los ejemplos siguientes se muestra cómo se pueden ver afectadas todas las filas si no se utiliza una cláusula WHERE para especificar la fila o las filas que se deben actualizar.

En este ejemplo se actualizan los valores de las columnas Bonus, CommissionPct y SalesQuota de todas las filas de la tabla SalesPerson.

USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET Bonus = 6000, CommissionPct = .10, SalesQuota = NULL;
GO

También se pueden utilizar valores calculados en una instrucción UPDATE. En el ejemplo siguiente se duplica el valor de la columna ListPrice para todas las filas de la tabla Product.

USE AdventureWorks ;
GO
UPDATE Production.Product
SET ListPrice = ListPrice * 2;
GO

B. Utilizar la instrucción UPDATE con una cláusula WHERE

En el ejemplo siguiente se utiliza la cláusula WHERE para especificar las filas que se van a actualizar. Por ejemplo, Adventure Works Cycles vende su modelo de bicicleta Road-250 en dos colores: rojo y negro. La compañía ha decidido cambiar el color rojo de este modelo por el rojo metalizado. La instrucción siguiente actualiza las filas de la tabla Production.Product para todos los productos Road-250 de color rojo.

USE AdventureWorks;
GO
UPDATE Production.Product
SET Color = N'Metallic Red'
WHERE Name LIKE N'Road-250%' AND Color = N'Red';
GO

C. Utilizar la instrucción UPDATE con información de otra tabla

En este ejemplo se modifica la columna SalesYTD de la tabla SalesPerson para reflejar las ventas más recientes registradas en la tabla SalesOrderHeader.

USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + SubTotal
FROM Sales.SalesPerson AS sp
JOIN Sales.SalesOrderHeader AS so
    ON sp.SalesPersonID = so.SalesPersonID
    AND so.OrderDate = (SELECT MAX(OrderDate)
                        FROM Sales.SalesOrderHeader 
                        WHERE SalesPersonID = 
                              sp.SalesPersonID);
GO

En el ejemplo anterior se asume que sólo se registra una venta para un determinado vendedor en una fecha determinada y que las actualizaciones son recientes. Si se puede registrar más de una venta para un vendedor determinado el mismo día, el ejemplo que se muestra no funcionará correctamente. Se ejecuta sin errores, pero cada valor de SalesYTD se actualiza con una sola venta, independientemente del número de ventas que se produjeron ese día realmente. Esto es debido a que una sola instrucción UPDATE nunca actualiza la misma fila dos veces.

Si puede haber más de una venta el mismo día para un vendedor especificado, todas las ventas de cada vendedor se deben agregar en la instrucción UPDATE, tal como se muestra en este ejemplo:

USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + 
    (SELECT SUM(so.SubTotal) 
     FROM Sales.SalesOrderHeader AS so
     WHERE so.OrderDate = (SELECT MAX(OrderDate)
                           FROM Sales.SalesOrderHeader AS so2
                           WHERE so2.SalesPersonID = 
                                 so.SalesPersonID)
     AND Sales.SalesPerson.SalesPersonID = so.SalesPersonID
     GROUP BY so.SalesPersonID);
GO

D. Utilizar UPDATE con la cláusula TOP

En el ejemplo siguiente se actualiza un 25 por ciento la columna VacationHours en 10 filas aleatorias de la tabla Employee.

USE AdventureWorks;
GO
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25 ;
GO

E. Utilizar UPDATE con la cláusula OUTPUT

En el ejemplo siguiente se actualiza un 25 por ciento la columna VacationHours de las 10 primeras filas de la tabla Employee. La cláusula OUTPUT devuelve el valor de VacationHours antes de aplicar la instrucción UPDATE en la columna DELETED.VacationHours y el valor actualizado en la columna INSERTED.VacationHours en la variable table@MyTableVar.

A continuación, dos instrucciones SELECT devuelven los valores de @MyTableVar y los resultados de la operación de actualización en la tabla Employee. Observe que los resultados de la columna INSERTED.ModifiedDate son diferentes de los valores de la columna ModifiedDate de la tabla Employee . Esto se debe a que el desencadenador AFTER UPDATE que actualiza el valor de ModifiedDate con la fecha actual se define en la tabla Employee. Sin embargo, las columnas devueltas de OUTPUT reflejan los datos antes de activarse los desencadenadores. Para obtener más ejemplos donde se utiliza la cláusula OUTPUT, vea OUTPUT (cláusula de Transact-SQL).

USE AdventureWorks;
GO
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    OldVacationHours int,
    NewVacationHours int,
    ModifiedDate datetime);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25 
OUTPUT INSERTED.EmployeeID,
       DELETED.VacationHours,
       INSERTED.VacationHours,
       INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

F. Utilizar UPDATE con la cláusula WITH common_table_expression

En el ejemplo siguiente se actualiza el valor de VacationHours un 25 por ciento para todos los empleados que mantienen informado a ManagerID``12 directa o indirectamente. La expresión de tabla común devuelve una lista jerárquica de los empleados que dependen directamente de ManagerID``12 y los empleados que dependen de esos empleados, y así sucesivamente. Sólo se modifican las filas devueltas por la expresión de tabla común. Para obtener información sobre las expresiones de tabla comunes recursivas, vea Consultas recursivas mediante expresiones de tabla comunes.

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

G. Utilizar UPDATE con la cláusula .WRITE para modificar los datos de una columna de tipo nvarchar(max)

En el ejemplo siguiente se utiliza la cláusula **.**WRITE para actualizar un valor parcial de DocumentSummary, una columna de tipo nvarchar(max) de la tabla Production.Document . La palabra components se sustituye por la palabra featuresal especificar la palabra sustituta, la ubicación inicial (desplazamiento) de la palabra que se va a sustituir en los datos existentes y el número de caracteres que se va a sustituir (longitud). En el ejemplo se utiliza también la cláusula OUTPUT para devolver las imágenes anterior y posterior de la columna DocumentSummary en la variable table@MyTableVar.

USE AdventureWorks;
GO
DECLARE @MyTableVar table (
    DocumentID int NOT NULL,
    SummaryBefore nvarchar(max),
    SummaryAfter nvarchar(max));
UPDATE Production.Document
SET DocumentSummary .WRITE (N'features',28,10)
OUTPUT INSERTED.DocumentID,
       DELETED.DocumentSummary, 
       INSERTED.DocumentSummary 
    INTO @MyTableVar
WHERE DocumentID = 3 ;
SELECT DocumentID, SummaryBefore, SummaryAfter 
FROM @MyTableVar;
GO

H. Utilizar UPDATE con .WRITE para agregar y quitar datos en una columna de tipo nvarchar(max)

En los ejemplos siguientes se agregan y quitan datos en una columna de tipo nvarchar(max) que tiene un valor establecido actualmente en NULL. Dado que no se puede utilizar la cláusula **.**WRITE para modificar una columna NULL, primero se llena la columna con datos temporales. Después, estos datos se sustituyen por los datos correctos mediante la cláusula .WRITE. En los demás ejemplos se anexan datos al final del valor de la columna, se quitan (truncan) los datos de la columna y, por último, se quitan los datos parciales de la columna. Las instrucciones SELECT muestran la modificación de datos resultante de cada instrucción UPDATE.

USE AdventureWorks;
GO
-- Replacing NULL value with temporary data.
UPDATE Production.Document
SET DocumentSummary = N'Replacing NULL value'
WHERE DocumentID = 1;
GO
SELECT DocumentSummary 
FROM Production.Document
WHERE DocumentID = 1;
GO
-- Replacing temporary data with the correct data. Setting @Length to NULL 
-- truncates all existing data from the @Offset position.
UPDATE Production.Document
SET DocumentSummary .WRITE(N'Carefully inspect and maintain the tires and crank arms.',0,NULL)
WHERE DocumentID = 1;
GO
SELECT DocumentSummary 
FROM Production.Document
WHERE DocumentID = 1;
GO
-- Appending additional data to the end of the column by setting 
-- @Offset to NULL.
UPDATE Production.Document
SET DocumentSummary .WRITE (N' Appending data to the end of the column.', NULL, 0)
WHERE DocumentID = 1;
GO
SELECT DocumentSummary 
FROM Production.Document
WHERE DocumentID = 1;
GO
-- Removing all data from @Offset to the end of the existing value by 
-- setting expression to NULL. 
UPDATE Production.Document
SET DocumentSummary .WRITE (NULL, 56, 0)
WHERE DocumentID = 1;
GO
SELECT DocumentSummary 
FROM Production.Document
WHERE DocumentID = 1;
GO
-- Removing partial data beginning at position 9 and ending at 
-- position 21.
UPDATE Production.Document
SET DocumentSummary .WRITE ('',9, 12)
WHERE DocumentID = 1;
GO
SELECT DocumentSummary 
FROM Production.Document
WHERE DocumentID = 1;
GO

I. Utilizar UPDATE con OPENROWSET para modificar una columna de tipo varbinary(max)

En el ejemplo siguiente se sustituye por una imagen nueva una imagen almacenada en una columna de tipo varbinary(max). Se utiliza la función OPENROWSET con la opción BULK para cargar la imagen en la columna. En este ejemplo se da por supuesto que hay un archivo denominado Tires.jpg en la ruta de acceso especificada.

USE AdventureWorks;
GO
UPDATE Production.ProductPhoto
SET ThumbNailPhoto = (
    SELECT *
    FROM OPENROWSET(BULK 'c:\Tires.jpg', SINGLE_BLOB)AS x )
WHERE ProductPhotoID = 1;
GO

Vea también

Referencia

CREATE TABLE (Transact-SQL)
CREATE TRIGGER (Transact-SQL)
Cursores (Transact-SQL)
DELETE (Transact-SQL)
INSERT (Transact-SQL)
OPENROWSET (Transact-SQL)
Funciones de texto e imagen (Transact-SQL)
WITH common_table_expression (Transact-SQL)

Otros recursos

Actualizar los datos de una tabla

Ayuda e información

Obtener ayuda sobre SQL Server 2005