DELETE (Transact-SQL)

Actualizado: 14 de abril de 2006

Quita filas de una tabla o vista.

Convenciones de sintaxis de Transact-SQL

Sintaxis

[ WITH <common_table_expression> [ ,...n ] ]
DELETE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ FROM ] 
        { table_name [ WITH ( <table_hint_limited> [ ...n ] ) ]
        | view_name 
        | rowset_function_limited 
        | table_valued_function
    }
    [ <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 el conjunto de resultados de 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.

  Las expresiones de tabla comunes también se pueden utilizar con las instrucciones SELECT, INSERT, UPDATE 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 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 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).

• FROM
  Se trata de una palabra clave opcional que se puede utilizar entre la palabra clave DELETE y el destino table_or_view_name o rowset_function_limited.

• 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
  Es el nombre de la tabla cuyas filas se deben quitar.

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

• WITH ( <table_hint_limited> [... n] )
  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).

• view_name
  Es el nombre de la vista cuyas filas se deben quitar.

  La vista a la que hace referencia 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
  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.

• table_valued_function
  Puede ser una función con valores de tabla. Para obtener más información acerca de las funciones con valores de tabla, vea Funciones definidas por el usuario con valores de tabla.

• <OUTPUT_Clause>
  Devuelve filas eliminadas, o expresiones basadas en ellas, como parte de la operación DELETE. 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 una cláusula FROM adicional. Esta extensión de Transact-SQL para DELETE permite especificar datos de <table_source> y eliminar las filas correspondientes de la tabla en la primera cláusula FROM.

  Se puede utilizar esta extensión, que especifica una combinación, en lugar de una subconsulta en la cláusula WHERE para identificar las filas que se van a quitar.

  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
  Es el nombre del cursor abierto desde el que se realiza 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. La variable de cursor debe hacer referencia a un cursor que permita realizar actualizaciones.

• OPTION ( <query_hint> [ ,... n] )
  Son 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 Query Hint (Transact-SQL).

Notas

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

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.

La configuración de la opción SET ROWCOUNT se ignora en las instrucciones DELETE con tablas remotas y vistas divididas remotas y locales.

Si desea eliminar todas las filas de una tabla, use la instrucción DELETE sin especificar una cláusula WHERE o use TRUNCATE TABLE. TRUNCATE TABLE es más rápido que DELETE y utiliza menos recursos de los registros de transacciones y de sistema.

Eliminar filas de un montón

Cuando se eliminan filas de un montón, Database Engine (Motor de base de datos) puede usar 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 de los bloqueos, vea Bloquear el motor de base de datos.

Utilizar un desencadenador INSTEAD OF en acciones DELETE

Cuando se define un desencadenador INSTEAD OF en las acciones DELETE en una tabla o vista, se ejecuta el desencadenador en lugar de la instrucción DELETE. Las versiones anteriores de SQL Server sólo admitían desencadenadores AFTER en DELETE y otras instrucciones de modificación de datos. No se puede especificar la cláusula FROM en una instrucción DELETE 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).

Permisos

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

Los permisos para utilizar DELETE 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 DELETE sin la cláusula WHERE

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

B. Utilizar DELETE en un conjunto de 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

C. Utilizar DELETE en la fila actual de un cursor

En el ejemplo siguiente se elimina una fila única de la tabla EmployeePayHistory mediante un cursor denominado complex_cursor. La operación de eliminación sólo afecta a la única fila que se recupera 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 la instrucción DELETE basada en una subconsulta y utilizar la extensión de Transact-SQL

En este ejemplo 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 SQL-2003 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

A. Utilizar DELETE con la cláusula TOP

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

A. Utilizar DELETE con la cláusula OUTPUT

En el ejemplo siguiente se eliminan todas las filas de la tabla Sales.ShoppingCartItem. La cláusula OUTPUT DELETED.* INTO @MyTableVar especifica que se devuelvan a la variable @MyTableVartable los resultados de la instrucción DELETE, es decir, todas las columnas de las filas eliminadas. A continuación, dos instrucciones SELECT devuelven los valores de @MyTableVar y los resultados de la operación de eliminación en la tabla ShoppingCartItem.

USE AdventureWorks;
GO
DELETE Sales.ShoppingCartItem
    OUTPUT DELETED.* ;

--Verify all rows in the table have been deleted.
SELECT COUNT(*) AS [Rows in Table] FROM Sales.ShoppingCartItem;
GO

Estos son los conjuntos de resultados de las instrucciones SELECT:

ShoppingCartItemID  ShoppingCartID  Quantity  ProductID 
------------------  --------------  --------  ---------
2                   14951           3         862
5                   20621           7         874
4                   20621           4         881

(3 row(s) affected)

Rows in Table 
------------- 
0

(1 row(s) affected)

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

Otros recursos

Eliminar datos de una tabla

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios

Versión	Historial
14 de abril de 2006	Contenido nuevo: Se agregó la sección "Eliminar filas de un montón" a la sección Notas.