.png)
DELETE (Transact-SQL)
Quita una o varias filas de una tabla o vista en SQL Server 2008.
[ 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
}
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).
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.
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).
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.
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.
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.
|
Categoría |
Elementos de sintaxis ofrecidos |
|---|---|
|
DELETE |
|
|
Cláusula WHERE • TOP • cláusula FROM y subconsultas • cursor • expresión de tabla común WITH |
|
|
Vistas • variables de tabla |
|
|
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 |
|
Cláusula OUTPUT |
|
|
Utilizar DELETE en otras instrucciones |
Procedimiento almacenado • MERGE |
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
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.
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.
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
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
