DELETE (Transact-SQL)

Quita una o varias filas de una tabla o vista en SQL Server 2008.

Convenciones de sintaxis de Transact-SQL

Sintaxis

[ WITH common_table_expression [ ,...n ] ]
DELETE 
    [ TOP (expression ) [ PERCENT ] ] 
    [ FROM ] 
    { { table_alias
      | <object> 
      | rowset_function_limited 
      [ WITH (table_hint_limited [ ...n ] ) ] } 
      | @table_variable
    }
    [ OUTPUT Clause ]
    [ FROMtable_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 el conjunto de resultados con nombre temporal, también conocido como expresión de tabla común, definido dentro del ámbito de la instrucción DELETE. El conjunto de resultados se deriva de una instrucción SELECT. Para obtener más información, vea WITH common_table_expression (Transact-SQL).

• TOP ( expression) [ PERCENT ]
  Especifica el número o porcentaje de filas aleatorias que se van a eliminar. 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 DELETE no están ordenadas por ningún concepto.

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

• FROM
  Palabra clave opcional que se puede utilizar entre la palabra clave DELETE y el objeto de destino.

• table_alias
  Alias especificado en la cláusula FROM de table_source que representa la tabla o vista de la que se van a eliminar las filas.

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

  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 vista. Para obtener más información acerca de las vistas que se pueden actualizar, vea CREATE VIEW (Transact-SQL).

• rowset_function_limited
  La función OPENQUERY o la función OPENROWSET, dependiendo de la funcionalidad 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 [... n] )
  Especifica una o varias sugerencias de tabla permitidas 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).

• @table\_variable
  Especifica una variable de tabla.

• <OUTPUT_Clause>
  Devuelve filas eliminadas, o expresiones basadas en ellas, como parte de la operación de eliminación. 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 una cláusula FROM adicional que se puede utilizar para unir el table_or view_name de destino con <table_source> para identificar las filas que se van a quitar. Esta extensión de Transact-SQL a DELETE se puede utilizar en lugar de una subconsulta en la cláusula WHERE.

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

• WHERE
  Especifica las condiciones utilizadas para limitar el número de filas que se van a eliminar. Si no se proporciona una cláusula WHERE, DELETE quita todas las filas de la tabla.

  Hay dos formas de operaciones de eliminación, que se basan en las condiciones que se especifiquen en la cláusula WHERE:

  • Las eliminaciones por búsqueda especifican una condición de búsqueda que califica las filas que se van a eliminar. Por ejemplo, WHERE column_name = value.

  • Las eliminaciones por posición utilizan la cláusula CURRENT OF para especificar un cursor. La operación de eliminación se produce en la posición actual del cursor. Este método puede ser más preciso que una instrucción DELETE por búsqueda que utilice una cláusula WHERE search_condition para calificar las filas que se van a eliminar. Una instrucción DELETE por búsqueda elimina varias filas si la condición de búsqueda no identifica exclusivamente una única fila.

• search_condition
  Especifica las condiciones restrictivas de las filas que se van a eliminar. No hay límite en el número de predicados que se pueden incluir en una condición de búsqueda. Para obtener más información, vea Condiciones de búsqueda (Transact-SQL).

• CURRENT OF
  Especifica que la instrucción DELETE se ejecutará 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 realiza 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. La variable de cursor debe hacer referencia a un cursor que permita realizar actualizaciones.

• OPTION (query_hint [ ,... n] )
  Palabras clave que indican que se utilizan sugerencias del optimizador para personalizar el procesamiento de la instrucción por parte del Database Engine (Motor de base de datos). Para obtener más información, vea Sugerencias de consulta (Transact-SQL).

Prácticas recomendadas

Para eliminar todas las filas de una tabla, utilice TRUNCATE TABLE. TRUNCATE TABLE es más rápido que DELETE y utiliza menos recursos de los registros de transacciones y del sistema.

Utilice la función @@ROWCOUNT para devolver a la aplicación cliente el número de filas eliminadas. Para obtener más información, vea @@ROWCOUNT (Transact-SQL).

Soporte de compatibilidad

La utilización de SET ROWCOUNT no afectará a las instrucciones DELETE en la próxima versión de SQL Server. No use SET ROWCOUNT con las instrucciones DELETE en los nuevos trabajos de desarrollo y modifique las aplicaciones que la utilizan en la actualidad de forma que utilicen la sintaxis TOP.

Control de errores

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

La instrucción DELETE puede tener un error si infringe un desencadenador o intenta quitar una fila a la que hacen referencia datos de otra tabla con una restricción FOREIGN KEY. Si la instrucción DELETE quita varias filas y cualquiera de las filas eliminadas infringe un desencadenador o restricción, se cancela la instrucción, se devuelve un error y no se elimina ninguna fila.

Cuando una instrucción DELETE encuentra un error aritmético (desbordamiento, división entre cero o error de dominio) al evaluar una expresión, el Database Engine (Motor de base de datos) trata ese error como si SET ARITHABORT fuese ON. Se cancela el resto del proceso por lotes y se devuelve un mensaje de error.

Interoperabilidad

Es posible utilizar DELETE en el cuerpo de una función definida por el usuario si el objeto que se va a modificar es una variable de tabla.

Cuando se elimina una fila que contiene una columna FILESTREAM, también se eliminan sus archivos de sistema de archivos subyacentes. El recolector de elementos no utilizados de FILESTREAM quita los archivos subyacentes. Para obtener más información, vea Administrar datos FILESTREAM usando Transact-SQL.

No se puede especificar la cláusula FROM en una instrucción DELETE 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).

Limitaciones y restricciones

Cuando TOP se utiliza con DELETE, las filas a que se hace referencia no están ordenadas por ningún concepto y la cláusula ORDER BY no se puede especificar directamente en esta instrucción. Si necesita utilizar TOP para eliminar filas por un orden cronológico significativo, debe utilizarla junto con una cláusula ORDER BY en una instrucción de subselección. Vea la sección Ejemplos más adelante en este tema.

TOP no se puede utilizar en una instrucción DELETE frente a vistas con particiones.

La configuración de la opción SET ROWCOUNT se omite en las instrucciones DELETE frente a tablas remotas y vistas remotas y locales con particiones.

Comportamiento del bloqueo

Cuando se eliminan filas de un montón, el Database Engine (Motor de base de datos) puede usar el bloqueo de filas o páginas para la operación. Como consecuencia, las páginas que han quedado vacías por la operación de eliminación permanecen asignadas al montón. Si no se cancela la asignación de las páginas vacías, otros objetos de la base de datos no pueden volver a utilizar el espacio asociado. Para eliminar las filas de un montón y cancelar la asignación de las páginas, use uno de los métodos siguientes.

• Especifique la sugerencia TABLOCK en la instrucción DELETE. Si se utiliza la sugerencia TABLOCK, la operación de eliminación aplica un bloqueo compartido a la tabla, en lugar de un bloqueo de fila o de página. Esto permite cancelar la asignación de las páginas. Para obtener más información acerca de la sugerencia TABLOCK, vea Sugerencias de tabla (Transact-SQL).

• Se debe utilizar TRUNCATE TABLE si se van a eliminar todas las filas de la tabla.

• Cree un índice agrupado en el montón antes de eliminar las filas. Puede quitar el índice agrupado tras eliminar las filas. Este método requiere más tiempo que los métodos anteriores y utiliza más recursos temporales.

Para obtener más información acerca del bloqueo, vea Bloquear el motor de base de datos.

Comportamiento del registro

La instrucción DELETE siempre se registra por completo.

Permisos

Se requieren permisos DELETE en la tabla de destino. También se requieren permisos para utilizar SELECT si la instrucción contiene una cláusula WHERE.

Los permisos DELETE se adjudican de forma predeterminada a los miembros de la función fija de servidor sysadmin, a 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

Categoría	Elementos de sintaxis ofrecidos
Sintaxis básica	DELETE
Especificar las filas que se van a eliminar	Cláusula WHERE • TOP • cláusula FROM y subconsultas • cursor • expresión de tabla común WITH
Especificar objetos de destino que no sean tablas estándar	Vistas • variables de tabla
Eliminar filas en una tabla remota	Servidor vinculado • función de conjunto de filas OPENQUERY • función de conjunto de filas OPENDATASOURCE
Invalidar el comportamiento predeterminado del optimizador de consultas mediante sugerencias	Sugerencias de tabla
Capturar los resultados de la instrucción DELETE	Cláusula OUTPUT
Utilizar DELETE en otras instrucciones	Procedimiento almacenado • MERGE

Sintaxis básica

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

En el ejemplo siguiente se eliminan todas las filas de la tabla SalesPersonQuotaHistory porque no se utiliza una cláusula WHERE para limitar el número de filas eliminadas.

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

Especificar las filas que se van a eliminar

Los ejemplos en esta sección muestran métodos para limitar el número de filas eliminadas.

A. Usar la cláusula WHERE para limitar filas

En el ejemplo siguiente se eliminan todas las filas de la tabla ProductCostHistory en las que el valor de la columna StandardCost es superior a 1000.00.

USE AdventureWorks;
GO
DELETE FROM Production.ProductCostHistory
WHERE StandardCost > 1000.00;
GO

B. Usar la cláusula TOP

Puede utilizar la cláusula TOP para limitar el número de filas que se eliminan en una instrucción DELETE. Cuando se utiliza una cláusula TOP (n) con DELETE, la operación de eliminación se realiza en una selección aleatoria de n número de filas.

En el ejemplo siguiente se elimina el 2.5 por ciento de las 27 filas de la tabla ProductInventory.

USE AdventureWorks;
GO
DELETE TOP (2.5) PERCENT 
FROM Production.ProductInventory;
GO

En el ejemplo siguiente se eliminan 20 filas aleatorias de la tabla PurchaseOrderDetail cuyas fechas de vencimiento son anteriores al primero de julio de 2002.

USE AdventureWorks;
GO
DELETE TOP (20) 
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20020701';
GO

Si necesita utilizar TOP para eliminar filas por un orden cronológico significativo, debe utilizarla junto con ORDER BY en una instrucción de subselección. En el siguiente ejemplo se eliminan de la tabla PurchaseOrderDetail las 10 filas con las fechas de vencimiento más antiguas. Para garantizar que sólo se eliminen 10 filas, la columna especificada en la instrucción de subselección (PurchaseOrderID) es la clave principal de la tabla. El uso de una columna sin clave en la instrucción de subselección podría causar la eliminación de más de 10 filas si la columna especificada contiene valores duplicados.

USE AdventureWorks;
GO
DELETE FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderDetailID IN
   (SELECT TOP 10 PurchaseOrderDetailID 
    FROM Purchasing.PurchaseOrderDetail 
    ORDER BY DueDate ASC);
GO

C. Utilizar un cursor para eliminar la fila actual

En el ejemplo siguiente se elimina una sola fila de la tabla EmployeePayHistory utilizando un cursor denominado complex_cursor. La operación de eliminación solo afecta a la única fila que se captura actualmente del cursor.

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;
DELETE FROM HumanResources.EmployeePayHistory
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;
GO

D. Utilizar una subconsulta y utilizar la extensión FROM de Transact-SQL

En el ejemplo siguiente se muestra la extensión de Transact-SQL que se utiliza para eliminar registros de una tabla base que se basa en una combinación o subconsulta correlacionada. La primera instrucción DELETE muestra la solución de subconsulta compatible con ISO y la segunda instrucción DELETE muestra la extensión de Transact-SQL. Ambas consultas quitan filas de la tabla SalesPersonQuotaHistory basándose en las ventas del año hasta la fecha almacenadas en la tabla SalesPerson.

-- SQL-2003 Standard subquery

USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
WHERE SalesPersonID IN 
    (SELECT SalesPersonID 
     FROM Sales.SalesPerson 
     WHERE SalesYTD > 2500000.00);
GO

-- Transact-SQL extension
USE AdventureWorks;
GO
DELETE FROM Sales.SalesPersonQuotaHistory 
FROM Sales.SalesPersonQuotaHistory AS spqh
    INNER JOIN Sales.SalesPerson AS sp
    ON spqh.SalesPersonID = sp.SalesPersonID
WHERE sp.SalesYTD > 2500000.00;
GO

E. Usar una expresión de tabla común

En el ejemplo siguiente solo se eliminan las filas devueltas por la expresión de tabla común.

Especificar objetos de destino que no sean tablas estándar

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

Eliminar filas de una tabla remota

En los ejemplos de esta sección se muestra cómo eliminar 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.

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

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.

En el ejemplo siguiente se especifica la sugerencia de tabla READPAST. Cuando se especifica READPAST, los bloqueos tanto de fila como de página se omiten; esto hace que el Motor de base de datos no lea las filas y las páginas bloqueadas por otras transacciones. Para obtener más información, vea Sugerencias de tabla (Transact-SQL).

USE AdventureWorks;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO

Capturar los resultados de la instrucción DELETE

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 DELETE o de 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 DELETE con la cláusula OUTPUT

En el ejemplo siguiente se muestra cómo guardar los resultados de una instrucción DELETE en una variable de tabla.

USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
OUTPUT DELETED.* 
WHERE ShoppingCartID = 20621;

--Verify all rows in the table that match the WHERE clause have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem WHERE ShoppingCartID = 20621;
GO

B. Utilizar OUTPUT con from_table_name en una instrucción DELETE

En el ejemplo siguiente se eliminan las filas de la tabla ProductProductPhoto según los criterios de búsqueda definidos en la cláusula FROM de la instrucción DELETE. La cláusula OUTPUT devuelve columnas de la tabla que se elimina (DELETED.ProductID, DELETED.ProductPhotoID) y de la tabla Product. Esta información se utiliza en la cláusula FROM para especificar las filas que se deben eliminar.

USE AdventureWorks;
GO
DECLARE @MyTableVar table (
    ProductID int NOT NULL, 
    ProductName nvarchar(50)NOT NULL,
    ProductModelID int NOT NULL, 
    PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
       p.Name,
       p.ProductModelID,
       DELETED.ProductPhotoID
    INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p 
    ON ph.ProductID = p.ProductID 
    WHERE p.ProductModelID BETWEEN 120 and 130;

--Display the results of the table variable.
SELECT ProductID, ProductName, ProductModelID, PhotoID 
FROM @MyTableVar
ORDER BY ProductModelID;
GO

Vea también

Referencia

CREATE TRIGGER (Transact-SQL)

INSERT (Transact-SQL)

SELECT (Transact-SQL)

TRUNCATE TABLE (Transact-SQL)

UPDATE (Transact-SQL)

WITH common_table_expression (Transact-SQL)

Conceptos

Información general de FILESTREAM