Introducción a la sentencia Delete

La sentencia DELETE se utiliza para eliminar filas de una tabla de datos de SQL Server. En este artículo exploraremos cómo utilizar la sentencia DELETE. Discutiremos algunas de las mejores prácticas, limitaciones y terminaremos con varios ejemplos.

Este es el cuarto artículo de una serie de artículos. Puede empezar por el principio leyendo Introducción a las sentencias de modificación de datos de SQL Server.

Todos los ejemplos de esta lección se basan en Microsoft SQL Server Management Studio y en la base de datos AdventureWorks2012. Puedes empezar a utilizar estas herramientas gratuitas utilizando mi Guía de inicio en el uso de SQL Server

Antes de empezar

Aunque este artículo utiliza la base de datos AdventureWorks para sus ejemplos, he decidido crear una tabla de ejemplo para utilizarla dentro de la base de datos para ayudar a ilustrar mejor los ejemplos. Puedes encontrar el script que necesitarás ejecutar aquí.

Además, vamos a rellenar inicialmente la tabla con algunos datos utilizando la siguiente sentencia INSERT:

WITH topSalesPerson (FullName, SalesLastYear, City, rowguid)AS (SELECT S.FirstName + ' ' + S.LastName, S.SalesLastYear, S.City ,NEWID()FROM Sales.vSalesPerson SWHERE S.SalesLastYear > 1000000)INSERT INTO esqlSalesPerson (FullName, SalesLastYear, City, rowguid) SELECT FullName, SalesLastYear, City, rowguid FROM topSalesPerson

Puedes aprender más sobre la sentencia INSERT leyendo nuestro artículo Introducción a la sentencia INSERT.

Estructura básica de la sentencia DELETE

La sentencia DELETE se utiliza para cambiar los valores de las columnas.

Hay tres componentes en una sentencia DELETE:

1. La tabla de la que se desea eliminar filas.
2. El criterio utilizado para elegir las filas a eliminar.

El formato general de la sentencia DELECT es:

DELETEFROM tableNameWHERE searchCondition…

Ahora vamos a hacer algunos DELETEs de ejemplo, así que si no lo has hecho ya, ejecuta el script para crear la tabla esqlSalesPerson.

Ejemplo sencillo – Borrar todas las filas

Cuando se utiliza DELETE sin cláusula WHERE, se eliminan todas las filas de la tabla. Si queremos eliminar cada de esqlSalesPerson podríamos ejecutar:

DELETEFROM esqlSalesPerson

Recomiendo, para los propósitos de nuestro ejemplo, envolver el comando DELETE en una transacción para no tener que insertar repetidamente filas en la base de datos de ejemplo. Aquí verás la misma sentencia DELETE.

BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonSELECT COUNT(1) FROM esqlSalesPersonROLLBACK

La primera sentencia de recuento devuelve 13 filas; mientras que la segunda devuelve 0. Como he hecho ROLLBACK de la transacción, la operación de borrado no es permanente.

Ejemplo sencillo – Borrar una sola fila

Un caso de uso más realista es borrar una sola fila. En muchas aplicaciones, esto se consigue filtrando una sola fila.

Si se conoce la clave primaria, no hay problema, ya que la clave primaria es única y sirve para identificar positivamente cada fila.

Supongamos que queremos eliminar la fila de Jillian Carson. Para ello, emitiríamos lo siguiente:

DELETEFROM esqlSalesPersonWHERE SalesPersonID = 10095

Tal vez se pregunte cómo sabría la clave primaria.

Puede imaginar que si tuviera una aplicación web que enumerara a todos los vendedores, la cuadrícula podría contener el Nombre completo del vendedor, y las Ventas del último año, pero oculto en la cuadrícula también estaría su SalesPersonID.

Cuando un usuario seleccionara una fila en la cuadrícula, y eligiera eliminar la fila, la aplicación recuperaría la clave primaria oculta para la fila, y luego emitiría el comando de eliminación a la base de datos.

Ejemplo simple – Eliminación de múltiples filas

Supongamos que sólo queremos mostrar a los vendedores de alto rendimiento en la tabla esqlSalesPerson. Sólo queremos mantener a aquellos vendedores con ventas del último año mayores o iguales a 2.000.000 de dólares.

Como nuestra tabla contiene aquellos con menos de esta cantidad, tenemos que eliminarlos ejecutando lo siguiente:

DELETEFROM esqlSalesPersonWHERE SalesLastYear > 2000000.00

Puedes ejecutar el siguiente comando para probar el script dentro de una transacción:

BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonWHERE SalesLastYear < 2000000.00SELECT FullName, SalesLastYearFROM esqlSalesPersonROLLBACK

Aquí tienes el resultado de ejecutar el script:

La flecha azul muestra que originalmente había 13 registros, y la roja los restantes cuyas ventas son mayores o iguales a dos millones de dólares.

También puedes crear condiciones de filtrado más complejas. Más adelante en el artículo mostraremos cómo utilizar una subconsulta. También puede utilizar condiciones booleanas, en la cláusula WHERE al igual que lo haría con la sentencia SELECT.

Consideraciones sobre el uso de la sentencia DELETE

Para eliminar todas las filas de una tabla, utilice TRUNCATE TABLE. Es mucho más rápido que DELETE ya que registra los cambios. Pero hay diferencias clave (Ver DELETE no es TRUNCATE! más abajo)

Además, no olvide que puede utilizar la función @@ROWCOUNT para saber cuántas filas fueron eliminadas.

Manejo de errores

Si una sentencia DELETE lanza un error todas las filas son restauradas a su estado anterior a la ejecución de la sentencia. Si se lanza un error no se elimina ninguna fila.

Hay muchas razones por las que una sentencia delete puede fallar. Algunas de las más típicas son:

• Filas huérfanas – si se define una restricción FOREIGN KEY entre dos tablas, como parentTable y childTable, entonces DELETE una fila de parentTable causará un error si existen filas de childTable relacionadas con el parent. La forma de evitarlo es eliminar primero las filas correspondientes de la tabla hija y luego la fila de la tabla padre.
• Errores aritméticos – Si la evaluación de una expresión da lugar a un error aritmético, como la división por cero, el DELETE se cancela y no se elimina ninguna fila.

Comportamiento de los bloqueos

Una sentencia de eliminación coloca un bloqueo exclusivo (X) en la tabla. Esto significa que ninguna otra consulta puede modificar los datos de la tabla hasta que se complete la transacción DELETE.

Todavía puede leer los datos, pero necesita utilizar la sugerencia NOLOCK o el nivel de aislamiento de lectura no comprometida.

¡DELETE no es TRUNCATE!

¡Delete no es TRUNCATE! Utilice DELETE para eliminar una o más filas de una tabla. Sólo en situaciones especiales, como cuando se necesita para restablecer una tabla a su estado inicial debe considerar TRUNCATE.

Mucha gente confunde DELETE y TRUNCATE. Si tienes dudas, te recomiendo que leas mi post sobre ¿Cuál es la diferencia entre Truncar y Eliminar en SQL Server?

Ejemplo complejo – DELETE usando una subconsulta

También puedes crear sentencias de eliminación más complejas. Casi cualquier cláusula que usted puede escribir en una sentencia SELECT cláusula WHERE, se puede escribir en la sentencia DELETE, incluyendo subconsultas en la cláusula WHERE.

Vamos a hacer un ejemplo.

Supongamos que usted necesita para eliminar todas las personas de ventas que viven en los EE.UU. de la tabla esqlSalesPerson. Aunque nuestra tabla tiene Ciudad, no tiene país. Podemos evitarlo utilizando una subconsulta.

BEGIN TRANSACTIONSELECT COUNT(1) FROM esqlSalesPersonDELETEFROM esqlSalesPersonWHERE esqlSalesPerson.City IN (SELECT DISTINCT City FROM Person.Address A INNER JOIN Person.StateProvince S ON A.StateProvinceID = S.StateProvinceID AND S.CountryRegionCode = 'US' )SELECT FullName, SalesLastYearFROM esqlSalesPersonROLLBACK

Tenga en cuenta que he envuelto el ejemplo en una transacción para no borrar permanentemente mis datos de prueba.

La subconsulta es de color azul. Observe que no es diferente de cualquier otra subconsulta correlacionada que haya utilizado en el pasado.

La mecánica de esta sentencia DELETE es:

1. Buscar ciudades distintas para todas las personas que viven en los EE.UU.
2. Eliminar a los vendedores cuya ciudad está en esta lista.

El primer paso resulta de la subconsulta. El segundo paso elimina las filas según la cláusula WHERE.