DELETE (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

Usando um gatilho INSTEAD OF em ações DELETE

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

USE AdventureWorks2008R2;
GO
DELETE FROM Sales.SalesPersonQuotaHistory;
GO

B. Usando DELETE em um conjunto de linhas

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

C. Usando DELETE na linha atual de um 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

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

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

F. Usando DELETE com a cláusula OUTPUT

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

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

Conceitos

Outros recursos