Introdução à instrução Delete Statement

A instrução DELETE é usada para remover linhas de uma tabela de dados do SQL Server. Neste artigo vamos explorar como usar a instrução DELETE. Discutiremos algumas das melhores práticas, limitações, e terminamos com vários exemplos.

Este é o quarto artigo de uma série de artigos. Você pode começar lendo Introduction to SQL Server Data Modification Statements.

Todos os exemplos para esta lição são baseados no Microsoft SQL Server Management Studio e no banco de dados AdventureWorks2012. Você pode começar a usar estas ferramentas gratuitas usando meu Guia de Introdução ao Uso do SQL Server

Antes de começarmos

Embora este artigo use a base de dados AdventureWorks para seus exemplos, decidi criar uma tabela de exemplos para uso dentro da base de dados para ajudar a ilustrar melhor os exemplos. Você pode encontrar o script que você precisa executar aqui.

Além disso, vamos inicialmente preencher a tabela com alguns dados usando o seguinte comando 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

Você pode aprender mais sobre o comando INSERT lendo nosso artigo Introduction to the INSERT Statement.

Estrutura básica da instrução DELETE

A instrução DELETE é usada para alterar os valores das colunas.

Existem três componentes para uma instrução DELETE:

1. A tabela da qual você deseja remover linhas.
2. O critério usado para escolher as linhas a remover.

O formato geral para a declaração DELECT é:

DELETEFROM tableNameWHERE searchCondition…

Agora vamos fazer alguns exemplos de DELETEs, então se você ainda não o fez, execute o script para criar a tabela esqlSalesPerson.

Simple Example – Deleting Every Row

Quando você usa DELETE sem uma cláusula WHERE, ele remove todas as linhas da tabela. Se quisermos apagar todas da esqlSalesPerson poderíamos executar:

DELETEFROM esqlSalesPerson

Iria recomendar, para os propósitos do nosso exemplo, que o comando DELETE fosse embrulhado em uma transação para que você não tenha que inserir linhas repetidamente no banco de dados de exemplo. Aqui você verá o mesmo comando DELETE.

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

O primeiro comando de contagem retorna 13 linhas; enquanto que o segundo retorna 0. Como eu ROLLBACK a transação, a operação de exclusão não é permanente.

Exemplo simples – Excluindo uma única linha

Um caso de uso mais realista ele exclui uma única linha. Em muitas aplicações, isto é conseguido filtrando em uma única linha.

Se você conhece a chave primária, você está dourado, pois a chave primária é única e pretende identificar positivamente cada linha.

Suponha que queremos excluir a linha de Jillian Carson. Para fazer isso, nós publicaríamos o seguinte:

DELETEFROM esqlSalesPersonWHERE SalesPersonID = 10095

Você pode estar se perguntando como você saberia a chave primária.

Você pode imaginar se você tivesse uma aplicação web que listasse todos os vendedores, que a grade pode conter o nome completo do vendedor, e as vendas do ano passado, mas escondida na grade também seria o SalesPersonID deles.

Quando um usuário selecionava uma linha na grade, e escolhia remover a linha, a aplicação recuperaria a chave primária oculta para a linha, e então emitiria o comando delete para a base de dados.

Exemplo simples – Excluindo várias linhas

Suponha que só queremos mostrar os vendedores de alto desempenho na tabela esqlSalesPerson. Só queremos manter os vendedores com vendas do ano passado maiores ou iguais a $2,000,000.

Desde que a nossa tabela contenha aqueles com menos deste valor, precisamos removê-los executando o seguinte:

DELETEFROM esqlSalesPersonWHERE SalesLastYear > 2000000.00

Pode executar o seguinte comando para tentar o script dentro de uma transação:

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

Aqui está o resultado de executar o script:

A seta azul mostra que havia originalmente 13 registros, e a vermelha os restantes cujas vendas são maiores ou iguais a dois milhões de dólares.

É possível também criar condições de filtragem mais complexas. Mais adiante no artigo vamos mostrar como usar uma subconsulta. Você também pode usar condições booleanas, na cláusula WHERE tão bem quanto você usaria com a instrução SELECT.

Considerações usando a instrução DELETE

Para apagar todas as linhas de uma tabela, use a TRUNCATE TABLE. É muito mais rápido do que o DELETE, pois ele faz alterações de log. Mas existem diferenças chave (Veja DELETE não é TRUNCATE! abaixo)

Além disso, não se esqueça que você pode usar a função @@ROWCOUNT para descobrir quantas linhas foram excluídas.

Controle de erros

Se uma instrução DELETE lançar um erro todas as linhas são restauradas ao seu estado antes que a instrução seja executada. Se um erro for acionado, nenhuma linha será removida.

Existem muitas razões pelas quais uma instrução de exclusão pode falhar. Algumas das mais típicas incluem:

• Orphan Rows – se uma restrição FOREIGN KEY estiver definida entre duas tabelas, como parentTable e childTable, então DELETE uma linha parentTable causará um erro se as linhas childTable relacionadas ao pai existirem. Eles contornam isso para remover primeiro as linhas childTable correspondentes, depois a linha parentTable.
• Erros aritméticos – Se uma avaliação de expressão resultar em um erro aritmético, como dividir por zero, o DELETE é cancelado e nenhuma linha é removida.

Comportamento de travamento

Uma instrução delete coloca um bloqueio exclusivo (X) na tabela. Isto significa que nenhuma outra consulta pode modificar os dados da tabela até que a transação DELETE esteja concluída.

Você ainda pode ler os dados, mas precisa usar a dica NOLOCK ou ler o nível de isolamento não comprometido.

DELETE não é TRUNCATE!

Delete não é TRUNCATE! Use DELETE para remover uma ou mais linhas de uma tabela. Somente em situações especiais, como quando você precisa redefinir uma tabela para o seu estado inicial, você deve considerar TRUNCATE.

Muitas pessoas ficam DELETE e TRUNCATE misturadas. SE você estiver incerto, eu recomendaria ler meu post sobre Qual é a diferença entre Truncar e Excluir no SQL Server?

Exemplo Complexo – DELETE Usando uma SubQuery

Você também pode criar uma instrução de exclusão mais complexa. Praticamente qualquer cláusula que você possa escrever em uma instrução SELECT WHERE, pode ser escrita na instrução DELETE, incluindo subconsultas na instrução WHERE.

Vamos fazer um exemplo.

Suponha que você precise remover todos os vendedores que vivem nos EUA da tabela esqlSalesPerson. Embora nossa tabela tenha Cidade, ela não tem país. Podemos contornar isso usando uma 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

Por favor, eu embrulhei o exemplo em uma transação para não apagar permanentemente meus dados de teste.

A subconsulta é colorida de azul. Note que não é diferente de qualquer outra subconsulta correlata que você possa ter usado no passado.

A mecânica desta declaração DELETE é:

1. Conte cidades distintas para todas as pessoas que vivem nos EUA.
2. Vendedores daDELETE cuja cidade está nesta lista.

O primeiro passo resulta da subconsulta. O segundo passo elimina as linhas de acordo com a cláusula WHERE.