DELETE (Transact-SQL)

Remove linhas de uma tabela ou exibição.

Convenções de sintaxe Transact-SQL

Sintaxe

[ WITH <common_table_expression> [ ,...n ] ]
DELETE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ FROM ] 
    { <object> | rowset_function_limited 
      [ WITH ( <table_hint_limited> [ ...n ] ) ]
    }
    [ <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 o conjunto de resultados nomeados temporário, também conhecido como expressão de tabela comum, definido dentro do escopo da instrução DELETE. O conjunto de resultados é derivado de uma instrução SELECT.

  Também podem ser usadas expressões de tabela comuns com as instruções SELECT, INSERT, UPDATE e CREATE VIEW. Para obter mais informações, consulte WITH common_table_expression (Transact-SQL).

• TOP (expression) [ PERCENT ]
  Especifica o número ou porcentagem de linhas aleatórias que serão excluídas. expression pode ser um número ou uma porcentagem de linhas. As linhas referenciadas na expressão TOP usada com INSERT, UPDATE ou DELETE não são organizadas em qualquer ordem.

  São necessários parênteses delimitando expression em TOP nas instruções INSERT, UPDATE e DELETE. Para obter mais informações, consulte TOP (Transact-SQL).

• FROM
  É uma palavra-chave opcional que pode ser usada entre a palavra-chave DELETE e o table_or_view_name ou rowset_function_limited de destino.

• server_name
  É o nome do servidor (usando um nome de servidor vinculado ou a função OPENDATASOURCE como nome de servidor) no qual a tabela ou exibição está localizada. Se server_name for especificado, database_name e schema_name serão necessários.

• database_name
  É o nome do banco de dados.

• schema_name
  É o nome do esquema ao qual a tabela ou exibição pertence.

• table_or view_name
  É o nome da tabela ou exibição da qual as linhas serão removidas.

  Uma variável table, dentro de seu escopo, também pode ser usada como uma fonte de tabela em uma instrução DELETE.

  A exibição referenciada por table_or_view_name deve ser atualizável e referenciar exatamente uma tabela base na cláusula FROM da exibição. Para obter mais informações sobre exibições atualizáveis, consulte CREATE VIEW (Transact-SQL).

• rowset_function_limited
  É a função OPENQUERY ou OPENROWSET, sujeita aos recursos de provedor. Para obter mais informações sobre os recursos exigidos pelo provedor, consulte Requisitos de UPDATE e DELETE para provedores OLE DB.

• WITH ( <table_hint_limited> [... n] )
  Especifica uma ou mais dicas de tabela permitidas para uma tabela de destino. A palavra-chave WITH e parênteses são necessários. NOLOCK e READUNCOMMITTED não são permitidos. Para obter mais informações sobre dicas de tabela, consulte Dicas de tabela (Transact-SQL).

• <OUTPUT_Clause>
  Retorna linhas excluídas, ou expressões baseadas nelas, como parte da operação DELETE. A cláusula OUTPUT não possui suporte em nenhuma instrução DML destinada a exibições ou tabelas remotas. Para obter mais informações, consulte cláusula OUTPUT (Transact-SQL).

• FROM <table_source>
  Especifica uma cláusula FROM adicional. Essa extensão Transact-SQL para DELETE permite especificar dados de <table_source> e excluir as linhas correspondentes da tabela na primeira cláusula FROM.

  Essa extensão, especificando uma união, pode ser usada em vez de uma subconsulta na cláusula WHERE para identificar linhas a serem removidas.

  Para obter mais informações, consulte FROM (Transact-SQL).

• WHERE
  Especifica as condições usadas para limitar o número de linhas que são excluídas. Se uma cláusula WHERE não for fornecida, DELETE removerá todas as linhas da tabela.

  Há duas formas de excluir operações com base no que é especificado na cláusula WHERE:

  • Exclusões pesquisadas especificam um critério de pesquisa para qualificar as linhas a serem excluídas. Por exemplo, WHERE column_name = value.

  • Exclusões posicionadas usam a cláusula CURRENT OF para especificar um cursor. A operação de exclusão ocorre na posição atual do cursor. Isso pode ser mais preciso do que uma instrução DELETE pesquisada que usa uma cláusula WHERE search_condition para qualificar as linhas a serem excluídas. Uma instrução DELETE pesquisada exclui várias linhas se o critério de pesquisa não identificar exclusivamente uma única linha.

• <search_condition>
  Especifica os critérios de restrição para as linhas a serem excluídas. Não há nenhum limite para o número de predicados que podem ser incluídos em um critério de pesquisa. Para obter mais informações, consulte Critério de pesquisa (Transact-SQL).

• CURRENT OF
  Especifica que DELETE é executado na posição atual do cursor especificado.

• GLOBAL
  Especifica que cursor_name se refere a um cursor global.

• cursor_name
  É o nome do cursor aberto do qual a busca é feita. Se um cursor global e um cursor local com o mesmo nome cursor_name existirem, este argumento fará referência ao cursor global se GLOBAL for especificado; caso contrário, fará referência ao cursor local. O cursor deve permitir atualizações.

• cursor_variable_name
  É o nome de uma variável de cursor. A variável de cursor deve fazer referência a um cursor que permite atualizações.

• OPTION ( <query_hint> [ ,... n] )
  são usadas palavras-chave que indicam que as dicas de otimizador são usadas para personalizar o modo que o Mecanismo de Banco de Dados processa a instrução. Para obter mais informações, consulte dicas de consulta (Transact-SQL).

Comentários

DELETE poderá ser usado no corpo de uma função definida pelo usuário se o objeto modificado for uma variável table.

A instrução DELETE pode falhar se violar um gatilho ou tentar remover uma linha referenciada por dados em outra tabela com uma restrição FOREIGN KEY. Se DELETE remover várias linhas e qualquer uma das linhas removidas violar um gatilho ou uma restrição, a instrução será cancelada, um erro será retornado e nenhuma linha será removida.

Quando uma instrução DELETE encontra um erro aritmético (estouro, divisão por zero ou um erro de domínio) que ocorre durante a avaliação de expressão, o Mecanismo de Banco de Dados trata esses erros como se SET ARITHABORT estivesse definido como ON. O resto do lote é cancelado e uma mensagem de erro é retornada.

A configuração da opção SET ROWCOUNT é ignorada para instruções DELETE em tabelas remotas e exibições locais e remotas particionadas.

O uso de SET ROWCOUNT não afetará as instruções DELETE, INSERT e UPDATE em uma futura versão do SQL Server. Não use SET ROWCOUNT com instruções DELETE, INSERT e UPDATE em um novo trabalho de desenvolvimento e planeje modificar os aplicativos que a utilizam atualmente. Em vez disso, recomendamos o uso da cláusula TOP.

Para excluir todas as linhas de uma tabela, use a instrução DELETE sem especificar uma cláusula WHERE, ou use TRUNCATE TABLE. TRUNCATE TABLE é mais rápido que DELETE e usa menos recursos do sistema e do log de transações.

Excluindo linhas de um heap

Quando linhas são excluídas de um heap, o Mecanismo de Banco de Dados pode usar bloqueio de linha ou página para a operação. Como resultado, as páginas que ficaram vazias pela operação de exclusão permanecem alocadas no heap. Quando páginas vazias não são desalocadas, o espaço associado não pode ser usado novamente por outros objetos do banco de dados.

Para excluir linhas em um heap e desalocar páginas, use um dos seguintes métodos.

• Especifique a dica TABLOCK na instrução DELETE. Usar a dica TABLOCK faz com que a operação de exclusão use um bloqueio compartilhado na tabela em vez de um bloqueio de linha ou página. Isso permite que as páginas sejam desalocadas. Para obter mais informações sobre a dica TABLOCK, consulte Dicas de tabela (Transact-SQL).

• Use TRUNCATE TABLE se todas as linhas forem excluídas da tabela.

• Crie um índice clusterizado no heap antes de excluir as linhas. Você pode cancelar o índice clusterizado depois que as linhas forem excluídas. Esse método consome mais tempo do que os métodos anteriores e usa mais recursos temporários.

Para obter mais informações sobre bloqueios, consulte Bloqueios no mecanismo de banco de dados.

Usando um gatilho INSTEAD OF em ações DELETE

Quando um gatilho INSTEAD OF é definido em ações DELETE em uma tabela ou exibição, o gatilho é executado em vez da instrução DELETE. Versões anteriores do SQL Server só oferecem suporte a gatilhos AFTER em instruções DELETE e outras instruções de modificação de dados. A cláusula FROM não pode ser especificada em uma instrução DELETE quer referencie, direta ou indiretamente, uma exibição com um gatilho INSTEAD OF definido. Para obter mais informações sobre gatilhos INSTEAD OF, consulte CREATE TRIGGER (Transact-SQL).

Permissões

São necessárias permissões DELETE na tabela designada. Também serão necessárias permissões SELECT se a instrução tiver uma cláusula WHERE.

As permissões DELETE assumem como padrão os membros da função de servidor fixa sysadmin, as funções de banco de dados fixa db_owner e db_datawriter e o proprietário da tabela. Membros das funções sysadmin, db_owner e db_securityadmin, e o proprietário de tabela podem transferir permissões a outros usuários.

Exemplos

A. Usando DELETE sem a cláusula WHERE

O exemplo a seguir exclui todas as linhas de uma tabela SalesPersonQuotaHistory porque uma cláusula WHERE não é usada para limitar o número de linhas excluídas.

USE AdventureWorks2008R2;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

B. Usando DELETE em um conjunto de linhas

O exemplo a seguir exclui todas as linhas de uma tabela ProductCostHistory na qual o valor da coluna StandardCost é maior que 1000.00.

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

C. Usando DELETE na linha atual de um cursor

O exemplo a seguir exclui uma única linha da tabela EmployeePayHistory que usa um cursor nomeado complex_cursor. O operação de exclusão afeta somente a linha buscada atualmente pelo cursor.

USE AdventureWorks2008R2;
GO
DECLARE complex_cursor CURSOR FOR
    SELECT a.BusinessEntityID
    FROM HumanResources.EmployeePayHistory AS a
    WHERE RateChangeDate <> 
         (SELECT MAX(RateChangeDate)
          FROM HumanResources.EmployeePayHistory AS b
          WHERE a.BusinessEntityID = b.BusinessEntityID) ;
OPEN complex_cursor;
FETCH FROM complex_cursor;
DELETE FROM HumanResources.EmployeePayHistory
WHERE CURRENT OF complex_cursor;
CLOSE complex_cursor;
DEALLOCATE complex_cursor;
GO

D. Usando DELETE com base em uma subconsulta e usando a extensão Transact-SQL

O exemplo a seguir mostra a extensão Transact-SQL usada para excluir registros de uma tabela base que se baseia em uma junção ou subconsulta correlacionada. A primeira instrução DELETE mostra a solução ISO de subconsulta compatível e a segunda instrução DELETE mostra a extensão Transact-SQL. Ambas as consultas removem linhas de uma tabela SalesPersonQuotaHistory com base nas vendas acumuladas no ano armazenadas na tabela SalesPerson.

-- SQL-2003 Standard subquery

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

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

GO

E. Usando DELETE com a cláusula TOP

O exemplo a seguir exclui 2.5 por cento das linhas (27 linhas) na tabela ProductInventory.

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

F. Usando DELETE com a cláusula OUTPUT

O exemplo a seguir mostra como salvar os resultados de uma instrução DELETE em uma variável de tabela.

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

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

G. Usando OUTPUT com from_table_name em uma instrução DELETE

O exemplo a seguir exclui linhas da tabela ProductProductPhoto com base em critérios de pesquisa definidos na cláusula FROM da instrução DELETE. A cláusula OUTPUT retorna colunas da tabela que está sendo excluída, DELETED.ProductID, DELETED.ProductPhotoID, e colunas da tabela Product. É usada na cláusula FROM para especificar as linhas a serem excluídas.

USE AdventureWorks2008R2;
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

Consulte também

Referência

CREATE TRIGGER (Transact-SQL)

INSERT (Transact-SQL)

SELECT (Transact-SQL)

TRUNCATE TABLE (Transact-SQL)

UPDATE (Transact-SQL)

WITH common_table_expression (Transact-SQL)

Conceitos

Guia de introdução ao armazenamento FILESTREAM

Outros recursos

Excluindo dados em uma tabela