UPDATE (Transact-SQL)

Cambia los datos existentes en una o varias columnas de una tabla o vista de SQL Server 2008.

Convenciones de sintaxis de Transact-SQL

Sintaxis

[ WITH common_table_expression [...n] ]
UPDATE 
    [ TOP (expression) [ PERCENT ] ] 
    { { table_alias | <object> | rowset_function_limited 
        [ WITH (table_hint_limited [ ...n ] ) ]
      }
      | @table_variable
    }
    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
          | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
          | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
          | @variable=column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
        } [ ,...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 con nombre, que también se conoce como expresión de tabla común (CTE), definidos en el ámbito de la instrucción UPDATE. El conjunto de resultados se deriva de una instrucción SELECT y la instrucción UPDATE hace referencia al conjunto de resultados. Para obtener más información, vea WITH common_table_expression (Transact-SQL).

• TOP ( expression) [ PERCENT ]
  Especifica el número o porcentaje de las filas que se van 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 de una instrucción INSERT, UPDATE, MERGE o DELETE no presentan ningún orden.

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

• table_alias
  Alias especificado en la cláusula FROM que representa la tabla o vista cuyas filas se van a actualizar.

• server_name
  Nombre del servidor vinculado en el que se encuentra la tabla o vista. server_name puede especificarse como un nombre de servidor vinculado o usando la función OPENDATASOURCE.

  Cuando server_name se especifica como un servidor vinculado, se requieren database_name y schema_name. Cuando server_name se especifica con OPENDATASOURCE, database_name y schema_name pueden no aplicarse a todos los orígenes de datos y estar sujetos a las capacidades del proveedor OLE DB que tiene acceso al objeto remoto. Para obtener más información, vea Consultas distribuidas.

• database_name
  Nombre de la base de datos.

• schema_name
  Nombre del esquema al que pertenece la tabla o vista.

• table_or view_name
  Nombre de la tabla o vista cuyas filas se van a actualizar.

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

• rowset_function_limited
  Especifica la función OPENQUERY u OPENROWSET. El uso de estas funciones está sujeto a las capacidades del proveedor OLE DB que tiene acceso al objeto remoto. Para obtener más información, vea Consultas distribuidas.

• WITH (table_hint_limited)
  Especifica una o varias sugerencias de tabla permitidas para la tabla de destino. Las sugerencias de tabla invalidan el comportamiento predeterminado del optimizador de consultas durante la ejecución de la instrucción UPDATE. No se permiten NOLOCK ni READUNCOMMITTED. Para obtener más información acerca de las sugerencias de tabla, vea Sugerencias de tabla (Transact-SQL).

• @table\_variable
  Especifica una variable table como origen de la tabla.

• 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.

  [!NOTA]

  Cuando se hace referencia a los tipos de datos de caracteres Unicode nchar, nvarchar y ntext, debe agregarse como prefijo la letra mayúscula 'N' a 'expression'. Si no se especifica 'N', SQL Server convierte la cadena a la página de códigos que se corresponde con la intercalación predeterminada de la base de datos o columna. Los caracteres que no se encuentren en esta página de códigos se perderán. Para obtener más información, vea Usar datos Unicode.

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

• { += | -= | *= | /= | %= | &= | ^= | |= }
  Es un operador compuesto que se utiliza para ejecutar una operación y establecer el valor original en el resultado de dicha operación.

  +=    Sumar y asignar

  -=    Restar y asignar

  *=    Multiplicar y asignar

  /=    Dividir y asignar

  %=    Hallar el módulo y asignar

  &=    AND bit a bit y asignar

  ^=    XOR bit a bit y asignar

  |=    OR bit a bit y asignar

  Para obtener más información, vea Operadores compuestos (Transact-SQL).

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

• property_name | field_name
  Es un miembro de propiedad pública o un miembro de datos públicos de un tipo definido por el usuario. La expresión que suministra el valor debe poder convertirse implícitamente al tipo de la propiedad.

  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.

• method_name( argument [ ,... n] )
  Es un método mutador público no estático de udt_column_name que utiliza uno o varios argumentos. 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.

• .WRITE (expression,@Offset, @Length)
  Especifica que una sección del valor de column_name se va a modificar. expression sustituye a las unidades de @Length a partir de @Offset de column_name. Con esta cláusula solo 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, Database Engine (Motor de base de datos) devuelve un error. Si la suma de @Offset y @Length supera 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 es 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 "Actualizar tipos de datos de valores grandes".

• @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 las instrucciones DML dirigidas a vistas locales con particiones, vistas distribuidas con particiones, tablas remotas 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 solo 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 el alias de objeto.

• 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. 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.

• 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 realiza en la posición actual del cursor especificado.

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

• cursor_name
  Nombre del cursor abierto desde el que se debe realizar la captura. 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
  Nombre de una variable de cursor. cursor_variable_name debe hacer referencia a un cursor que permite 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 Sugerencias de consulta (Transact-SQL).

Prácticas recomendadas

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

Actúe con precaución al especificar la cláusula FROM para proporcionar los criterios de la operación de actualización. 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 solo haya un valor disponible para cada caso de columna que se actualice, es decir, si la instrucción UPDATE no es determinista. Esto puede provocar resultados inesperados. Por ejemplo, en la instrucción UPDATE del siguiente script, 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 PRIMARY KEY 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), (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), (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

Tipos de datos

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

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 solo 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. Esto se puede configurar en orígenes de datos ODBC o estableciendo propiedades o atributos de conexión. Para obtener más información, vea SET ANSI_PADDING (Transact-SQL).

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 solo los 200 primeros caracteres de la columna, mientras que una actualización completa eliminaría o modificaría todos los datos de la columna.

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.

El Database Engine (Motor de base de datos) convierte una actualización parcial en una 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 clúster 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).

Si se hace referencia a la columna modificada por la cláusula .WRITE en una cláusula OUTPUT, el valor completo de la columna, ya sea la imagen anterior de deleted.column_name o la imagen posterior de inserted.column_name, se devuelve a la columna especificada de la variable de tabla.

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

Actualizar datos FILESTREAM

Puede utilizar la instrucción UPDATE para actualizar un campo FILESTREAM de forma que tenga un valor NULL, un valor vacío o una cantidad relativamente pequeña de datos insertados. Sin embargo, una gran cantidad de datos se transmite por secuencias de manera más eficaz en un archivo si se utilizan interfaces de Win32. Cuando se actualiza un campo FILESTREAM, se modifican los datos de BLOB subyacentes en el sistema de archivos. Cuando un campo FILESTREAM está establecido en NULL, se eliminan los datos de BLOB asociados al campo. No se puede utilizar .WRITE() para realizar actualizaciones parciales de datos FILESTREAM. Para obtener más información, vea Información general de FILESTREAM.

Actualizar columnas de tipo text, ntext e image

Cuando se modifica 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, salvo que la columna se actualice con NULL. Si la instrucción UPDATE pudo cambiar más de una fila durante la actualización de la clave de agrupación en clústeres 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.

Para reemplazar o modificar bloques grandes 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 MicrosoftSQL 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.

Control de errores

Puede implementar el control de errores para la instrucción UPDATE especificando la instrucción en una construcción TRY…CATCH. Para obtener más información, vea Usar TRY...CATCH en Transact-SQL.

Si la actualización de una fila infringe una restricción o una regla, si 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 entre 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 clúster hace que el tamaño del mismo y de la fila sea superior a 8.060 bytes, la actualización no se produce y se devuelve un mensaje de error.

Interoperabilidad

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

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 solamente se admite la definición de desencadenadores AFTER en instrucciones UPDATE y otras instrucciones de modificación de datos.

Limitaciones y restricciones

No se puede especificar la cláusula FROM en una instrucción UPDATE que haga referencia, directa o indirectamente, a una vista que tenga definido un desencadenador INSTEAD OF. Para obtener más información acerca de los desencadenadores INSTEAD OF, vea CREATE TRIGGER (Transact-SQL).

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. La utilización de SET ROWCOUNT no afectará a las instrucciones UPDATE en la próxima versión de SQL Server. No use SET ROWCOUNT con las instrucciones UPDATE en los nuevos trabajos de desarrollo y modifique las aplicaciones que la utilizan en la actualidad de forma que utilicen la sintaxis TOP.

Cuando una expresión de tabla común (CTE) es el destino de una instrucción UPDATE, todas las referencias a la CTE de la instrucción deben coincidir. Por ejemplo, si la CTE tiene asignado un alias en la cláusula FROM, el alias se debe utilizar para obtener todas las otras referencias a la CTE. Se requieren referencias CTE inequívocas porque una CTE no tiene un identificador de objeto, que utiliza SQL Server para reconocer la relación implícita entre un objeto y su alias. Sin esta relación, el plan de consulta puede producir un comportamiento de la unión inesperado y resultados imprevistos de la consulta. En los ejemplos siguientes se muestran métodos correctos e incorrectos de especificar una CTE cuando la CTE es el objeto de destino de la operación de actualización.

USE tempdb;
GO
-- UPDATE statement with CTE references that are correctly matched.
DECLARE @x TABLE (ID int, Value int);
DECLARE @y TABLE (ID int, Value int);
INSERT @x VALUES (1, 10), (2, 20);
INSERT @y VALUES (1, 100),(2, 200);

WITH cte AS (SELECT * FROM @x)
UPDATE x -- cte is referenced by the alias.
SET Value = y.Value
FROM cte AS x  -- cte is assigned an alias.
INNER JOIN @y AS y ON y.ID = x.ID;
SELECT * FROM @x;
GO

Éste es el conjunto de resultados.

ID     Value

------ -----

1      100

2      200

(2 row(s) affected)

-- UPDATE statement with CTE references that are incorrectly matched.
USE tempdb;
GO
DECLARE @x TABLE (ID int, Value int);
DECLARE @y TABLE (ID int, Value int);
INSERT @x VALUES (1, 10), (2, 20);
INSERT @y VALUES (1, 100),(2, 200);

WITH cte AS (SELECT * FROM @x)
UPDATE cte   -- cte is not referenced by the alias.
SET Value = y.Value
FROM cte AS x  -- cte is assigned an alias.
INNER JOIN @y AS y ON y.ID = x.ID; 
SELECT * FROM @x; 
GO

Éste es el conjunto de resultados.

ID     Value

------ -----

1      100

2      100

(2 row(s) affected)

Comportamiento del registro

La instrucción UPDATE se registra por completo; sin embargo, 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 optimizado para cargas masivas de registros o el modelo de recuperación simple. El registro mínimo no se utiliza cuando se utiliza .WRITE para actualizar los valores existentes. Para obtener más información, vea Operaciones que pueden ser registradas mínimamente.

Seguridad

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 UPDATE se adjudican de forma predeterminada a los miembros de la función de servidor fija sysadmin, a las funciones de base de datos fijas 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

Categoría	Elementos de sintaxis ofrecidos
Sintaxis básica	UPDATE
Limitar las filas que se actualizan	WHERE • TOP • expresión de tabla común WITH • WHERE CURRENT OF
Establecer valores de columna	valores calculados • operadores compuestos • valores predeterminados • subconsultas
Especificar objetos de destino que no sean tablas estándar	vistas • variables de tabla • alias de tabla
Actualizar datos basados en datos de otras tablas	FROM
Actualizar filas en una tabla remota	servidor vinculado • OPENQUERY • OPENDATASOURCE
Actualizar tipos de datos de objetos grandes	.WRITE • OPENROWSET
Actualizar tipos definidos por el usuario	tipos definidos por el usuario
Invalidar el comportamiento predeterminado del optimizador de consultas mediante sugerencias	sugerencias de tabla • sugerencias de consulta
Capturar los resultados de la instrucción INSERT	cláusula OUTPUT
Utilizar UPDATE en otras instrucciones	procedimientos almacenados • TRY…CATCH

Sintaxis básica

Los ejemplos de esta sección muestran la funcionalidad básica de la instrucción UPDATE usando la sintaxis mínima requerida.

A. Utilizar una instrucción UPDATE simple

En el ejemplo siguiente se actualiza una sola columna para todas las filas de la tabla Person.Address.

USE AdventureWorks;
GO
UPDATE Person.Address
SET ModifiedDate = GETDATE();

B. Actualizar varias columnas

En el ejemplo siguiente 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

Limitar las filas que se actualizan

En los ejemplos de esta sección se muestran los medios que se pueden utilizar para limitar el número de filas afectadas por la instrucción UPDATE.

A. Usar la cláusula WHERE

En el ejemplo siguiente se utiliza la cláusula WHERE para especificar las filas que se van a actualizar. La instrucción actualiza el valor en la columna Color de la tabla Production.Product para todas las filas que tienen un valor existente 'Red' en la columna Color y un valor en la columna Name que empieza con 'Road-250'.

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

B. Usar la cláusula TOP

En los ejemplos siguientes se utiliza la cláusula TOP para limitar el número de filas que se modifican en una instrucción UPDATE. Cuando se utiliza una cláusula TOP (n) con UPDATE, la operación de actualización se realiza en una selección aleatoria de 'n' número de filas. En el ejemplo siguiente la columna VacationHours se actualiza un 25 por ciento en 10 filas aleatorias de la tabla Employee.

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

Si se debe utilizar TOP para aplicar actualizaciones por un orden cronológico, debe utilizarse junto con ORDER BY en una instrucción de subselección. En el siguiente ejemplo se actualizan las horas de vacaciones de los 10 empleados cuyas fechas de alta son más antiguas.

UPDATE HumanResources.Employee
SET VacationHours = VacationHours + 8
FROM (SELECT TOP 10 EmployeeID FROM HumanResources.Employee
     ORDER BY HireDate ASC) AS th
WHERE HumanResources.Employee.EmployeeID = th.EmployeeID;
GO

C. Usar la cláusula WITH common_table_expression

En el ejemplo siguiente el valor de VacationHours se actualiza un 25 por ciento para todos los empleados que dependen directa o indirectamente de ManagerID12. La expresión de tabla común devuelve una lista jerárquica de los empleados que dependen directamente de ManagerID12 y los empleados que dependen de esos empleados, y así sucesivamente. Solo se modifican las filas devueltas por la expresión de tabla común. Para obtener más 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

D. Usar la cláusula WHERE CURRENT OF

En el ejemplo siguiente se utiliza la cláusula WHERE CURRENT OF para actualizar solo la fila en la que está colocado el cursor. Cuando un cursor se basa en una combinación, solo se modifica la expresión table_name especificada en la instrucción UPDATE. Las demás tablas que participan en el cursor no se ven afectadas.

USE AdventureWorks;
GO
DECLARE complex_cursor CURSOR FOR
    SELECT a.EmployeeID
    FROM HumanResources.EmployeePayHistory AS a
    WHERE RateChangeDate <> 
         (SELECT MAX(RateChangeDate)
          FROM HumanResources.EmployeePayHistory AS b
          WHERE a.EmployeeID = b.EmployeeID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
UPDATE HumanResources.EmployeePayHistory
SET PayFrequency = 2 
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;
GO

Establecer valores de columna

En los ejemplos de esta sección se muestra cómo se actualizan columnas utilizando valores calculados, subconsultas y valores DEFAULT.

A. Especificar un valor calculado

En los ejemplos siguientes se usan valores calculados en una instrucción UPDATE. En el ejemplo 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. Especificar un operador compuesto

En el ejemplo siguiente se usa la variable @NewPrice para incrementar el precio de todas las bicicletas de color rojo, tomando como base el precio actual y sumándole 10.

USE AdventureWorks;
GO
DECLARE @NewPrice int = 10;
UPDATE Production.Product
SET ListPrice += @NewPrice
WHERE Color = N'Red';
GO

En el ejemplo siguiente se utiliza al operador compuesto += para anexar los datos ' - tool malfunction' al valor existente en la columna Name para las filas que tienen un ScrapReasonID comprendido entre 10 y 12.

USE AdventureWorks;
GO
UPDATE Production.ScrapReason 
SET Name += ' - tool malfunction'
WHERE ScrapReasonID BETWEEN 10 and 12;

C. Especificar una subconsulta en la cláusula SET

En el ejemplo siguiente se utiliza una subconsulta en la cláusula SET para determinar el valor que se ha de utilizar para actualizar la columna. La subconsulta debe devolver solamente un valor escalar (es decir, un valor único por fila). En el ejemplo se modifica la columna SalesYTD de la tabla SalesPerson para reflejar las ventas más recientes registradas en la tabla SalesOrderHeader. La subconsulta suma las ventas correspondientes a cada vendedor en la instrucción UPDATE.

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. Actualizar filas utilizando valores DEFAULT

En el ejemplo siguiente se establece la columna CostRate en su valor predeterminado (0.00) para todas las filas que tienen un valor de CostRate superior a 20.00.

USE AdventureWorks;
GO
UPDATE Production.Location
SET CostRate = DEFAULT
WHERE CostRate > 20.00;

Especificar objetos de destino que no sean tablas estándar

En los ejemplos de esta sección se muestra cómo actualizar filas especificando una vista, un alias de tabla o una variable de tabla.

A. Especificar una vista como objeto de destino

En el ejemplo siguiente se actualizan las filas de una tabla especificando una vista como objeto de destino. La definición de vista hace referencia a varias tablas; sin embargo, la instrucción UPDATE se ejecuta correctamente porque hace referencia a columnas de solo una de las tablas subyacentes. La ejecución de la instrucción UPDATE produciría un error si se especificaran columnas de ambas tablas. Para obtener más información, vea Modificar datos mediante una vista.

USE AdventureWorks;
GO
UPDATE Person.vStateProvinceCountryRegion
SET CountryRegionName = 'United States of America'
WHERE CountryRegionName = 'United States';

B. Especificar un alias de tabla como objeto de destino

En el ejemplo siguiente se actualizan filas de la tabla Production.ScrapReason. El alias de tabla asignado a ScrapReason en la cláusula FROM se especifica como objeto de destino en la cláusula UPDATE.

USE AdventureWorks;
GO
UPDATE sr
SET sr.Name += ' - tool malfunction'
FROM Production.ScrapReason AS sr
JOIN Production.WorkOrder AS wo 
     ON sr.ScrapReasonID = wo.ScrapReasonID
     AND wo.ScrappedQty > 300;

C. Especificar una variable de tabla como objeto de destino

En el ejemplo siguiente se actualizan filas en una variable de tabla.

USE AdventureWorks;
GO
-- Create the table variable.
DECLARE @MyTableVar table(
    EmpID int NOT NULL,
    NewVacationHours int,
    ModifiedDate datetime);

-- Populate the table variable with employee ID values from HumanResources.Employee.
INSERT INTO @MyTableVar (EmpID)
    SELECT EmployeeID FROM HumanResources.Employee;

-- Update columns in the table variable.
UPDATE @MyTableVar
SET NewVacationHours = e.VacationHours + 20,
    ModifiedDate = GETDATE()
FROM HumanResources.Employee AS e 
WHERE e.EmployeeID = EmpID;

-- Display the results of the UPDATE statement.
SELECT EmpID, NewVacationHours, ModifiedDate FROM @MyTableVar
ORDER BY EmpID;
GO

Actualizar datos basados en datos de otras tablas

En los ejemplos de esta sección se muestran métodos para actualizar filas de una tabla basándose en la información de otra tabla.

A. 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 supone que solo 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 el siguiente 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

Actualizar filas en una tabla remota

En los ejemplos de esta sección se muestra cómo actualizar filas en una tabla de destino remota utilizando un servidor vinculado o una función de conjunto de filas para hacer referencia a la tabla remota.

A. Actualizar datos en una tabla remota utilizando un servidor vinculado

En el ejemplo siguiente se actualiza una tabla en un servidor remoto. En el ejemplo se comienza creando un vínculo al origen de datos remoto mediante sp_addlinkedserver. El nombre del servidor vinculado, MyLinkServer, se especifica después como parte del nombre de objeto de cuatro partes con el formato servidor.catálogo.esquema.objeto. Observe que se debe especificar un nombre de servidor válido para @datasrc.

USE master;
GO
-- Create a link to the remote data source. 
-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.

EXEC sp_addlinkedserver @server = N'MyLinkServer',
    @srvproduct = N' ',
    @provider = N'SQLNCLI10', 
    @datasrc = N'<server name>',
    @catalog = N'AdventureWorks';
GO
USE AdventureWorks;
GO
-- Specify the remote data source using a four-part name 
-- in the form linked_server.catalog.schema.object.

UPDATE MyLinkServer.AdventureWorks.HumanResources.Department
SET GroupName = N'Public Relations'
WHERE DepartmentID = 4;

B. Actualizar datos en una tabla remota utilizando una función OPENQUERY

En el ejemplo siguiente se actualiza una fila en una tabla remota especificando la función de conjunto de filas OPENQUERY. En este ejemplo se usa el nombre del servidor vinculado creado en el ejemplo anterior.

UPDATE OPENQUERY (MyLinkServer, 'SELECT GroupName FROM HumanResources.Department WHERE DepartmentID = 4') 
SET GroupName = 'Sales and Marketing';

C. Actualizar datos en una tabla remota utilizando la función OPENDATASOURCE

En el ejemplo siguiente se inserta una fila en una tabla remota especificando la función de conjunto de filas OPENDATASOURCE. Especifique un nombre de servidor válido para el origen de datos utilizando el formato nombreDeServidor o nombreDeServidor\nombreDeInstancia. Es posible que sea necesario configurar la instancia de SQL Server para Consultas distribuidas ad hoc. Para obtener más información, vea consultas distribuidas ad hoc (opción).

UPDATE OPENQUERY (MyLinkServer, 'SELECT GroupName FROM HumanResources.Department WHERE DepartmentID = 4') 
SET GroupName = 'Sales and Marketing';

Actualizar tipos de datos de objetos grandes

En los ejemplos de esta sección se muestran métodos para actualizar valores en columnas que se definen con tipos de datos de objetos grandes (LOB).

A. Utilizar UPDATE con .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 features al 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 de tabla @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

B. 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 rellena 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

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

En el ejemplo siguiente se sustituye una imagen almacenada en una columna de tipo varbinary(max) por una imagen nueva. La función OPENROWSET se utiliza 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 de archivo especificada.

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

D. Usar UPDATE para modificar datos FILESTREAM

En el ejemplo siguiente se utiliza la instrucción UPDATE para modificar los datos en el archivo del sistema de archivos. No se recomienda este método para transmitir por secuencias grandes cantidades de datos a un archivo. Utilice las interfaces de Win32 adecuadas. En el ejemplo siguiente se reemplaza cualquier texto del registro del archivo por el texto Xray 1. Para obtener más información, vea Información general de FILESTREAM.

UPDATE Archive.dbo.Records
SET [Chart] = CAST('Xray 1' as varbinary(max))
WHERE [SerialNumber] = 2;

Actualizar tipos definidos por el usuario

En los ejemplos siguientes se modifican valores en columnas de tipo definido por el usuario (UDT) CLR. Se muestran tres métodos. Para obtener más información acerca de las columnas definidas por el usuario, vea Tipos definidos por el usuario de CLR.

A. Usar un tipo de datos del sistema

Se puede actualizar un UDT suministrando un valor en un tipo de datos del sistema de SQL Server, siempre que el tipo definido por el usuario admita la conversión implícita o explícita de 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 dbo.Cities
SET Location = CONVERT(Point, '12.3:46.2')
WHERE Name = 'Anchorage';

B. Invocar un método

Se puede actualizar un UDT invocando un método marcado como mutador, 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 dbo.Cities
SET Location.SetXY(23.5, 23.5)
WHERE Name = 'Anchorage';

C. Modificar el valor de una propiedad o un miembro de datos

Se puede actualizar un UDT modificando el valor de una propiedad registrada o un miembro de datos públicos 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 dbo.Cities
SET Location.X = 23.5
WHERE Name = 'Anchorage';

Invalidar el comportamiento predeterminado del optimizador de consultas mediante sugerencias

En los ejemplos de esta sección se muestra cómo usar sugerencias de tabla y sugerencias de consulta para invalidar de forma temporal el comportamiento predeterminado del optimizador de consultas cuando se procesa la instrucción UPDATE.

Advertencia
Como el optimizador de consultas de SQL Server suele seleccionar el mejor plan de ejecución de una consulta, se recomienda que únicamente los administradores de bases de datos y desarrolladores experimentados utilicen las sugerencias como último recurso.

A. Especificar una sugerencia de tabla

En el ejemplo siguiente se especifica la sugerencia de tabla TABLOCK. Esta sugerencia especifica que se aplique un bloqueo compartido a la tabla Production.Product y que se mantenga hasta que finalice la instrucción UPDATE.

USE AdventureWorks;
GO
UPDATE Production.Product
WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%';
GO

B. Especificar una sugerencia de consulta

En el ejemplo siguiente se especifica la sugerencia de consultaOPTIMIZE FOR (@variable) en la instrucción UPDATE. Esta sugerencia indica al optimizador de consultas que utilice un valor concreto para una variable local cuando la consulta se compila y optimiza. El valor se utiliza solo durante la optimización de la consulta y no durante la ejecución de la misma.

USE AdventureWorks;
GO
CREATE PROCEDURE Production.uspProductUpdate
@Product nvarchar(25)
AS
SET NOCOUNT ON;
UPDATE Production.Product
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE @Product
OPTION (OPTIMIZE FOR (@Product = 'BK-%') );
GO
-- Execute the stored procedure 
EXEC Production.uspProductUpdate 'BK-%';

Capturar los resultados de la instrucción INSERT

En los ejemplos de esta sección se muestra cómo usar la cláusula OUTPUT para devolver información de cada fila afectada por una instrucción UPDATE o expresiones que se basan en esta instrucción. Estos resultados se pueden devolver a la aplicación de procesamiento para que los utilice en mensajes de confirmación, archivado y otros requisitos similares de una aplicación.

A. Utilizar UPDATE con la cláusula OUTPUT

En el ejemplo siguiente la columna VacationHours de la tabla Employee se actualiza un 25 por ciento en las 10 primeras filas y también se establece en la fecha actual el valor de la columna ModifiedDate. 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 de la columna inserted.VacationHours en la variable de tabla @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. 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,
    ModifiedDate = GETDATE() 
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.
SELECT TOP (10) EmployeeID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO

Utilizar UPDATE en otras instrucciones

En los ejemplos de esta sección se muestra cómo utilizar UPDATE en otras instrucciones.

A. Utilizar UPDATE en un procedimiento almacenado

En el ejemplo siguiente se utiliza una instrucción UPDATE en un procedimiento almacenado. El procedimiento toma un parámetro de entrada @NewHours y un parámetro de salida @RowCount. El valor del parámetro @NewHours se utiliza en la instrucción UPDATE para actualizar la columna VacationHours de la tabla HumanResources.Employee. El parámetro de salida @RowCount se utiliza para devolver el número de filas afectado a una variable local. La expresión CASE se utiliza en la cláusula SET para determinar condicionalmente el valor que se establece para VacationHours. Cuando se paga al empleado cada hora (SalariedFlag = 0), VacationHours se establece en el número actual de horas más el valor especificado en @NewHours; de lo contrario, VacationHours se establece en el valor especificado en @NewHours.

USE AdventureWorks;
GO
CREATE PROCEDURE HumanResources.Update_VacationHours
@NewHours smallint,
@RowCount int OUTPUT
AS 
SET NOCOUNT ON;
UPDATE HumanResources.Employee
SET VacationHours = 
    ( CASE
         WHEN SalariedFlag = 0 THEN VacationHours + @NewHours
         ELSE @NewHours
       END
    )
WHERE CurrentFlag = 1;
SET @RowCount = @@ROWCOUNT;
GO

-- Execute the stored procedure and return the number of rows updated to the variable @RowCount
DECLARE @RowCount int;
EXECUTE HumanResources.Update_VacationHours 40, @RowCount OUTPUT;
SELECT @RowCount AS RowCount;

B. Utilizar UPDATE en un bloque TRY...CATCH

En el ejemplo siguiente se utiliza una instrucción UPDATE en un bloque TRY...CATCH para controlar los errores de ejecución que pueden producirse durante una operación de actualización. Para obtener más información, vea Usar TRY...CATCH en Transact-SQL.

USE AdventureWorks;
GO
BEGIN TRANSACTION;

BEGIN TRY
    -- Intentionally generate a constraint violation error.
    UPDATE HumanResources.Department
    SET Name = N'MyNewName'
    WHERE DepartmentID BETWEEN 1 AND 2;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

Vea también

Referencia

DELETE (Transact-SQL)

INSERT (Transact-SQL)

MERGE (Transact-SQL)

OPENROWSET (Transact-SQL)

WITH common_table_expression (Transact-SQL)

Historial de cambios

Contenido actualizado
Se ha agregado table_alias a la sintaxis como método para especificar la tabla de destino que se va a actualizar.
Se ha agregado información en la sección "Limitaciones y restricciones" sobre cómo utilizar la expresión de tabla común como destino de una instrucción UPDATE.