DELETE (Transact-SQL)
Remove uma ou mais linhas de uma tabela ou exibição no SQL Server.
Aplica-se a: SQL Server (SQL Server 2008 até a versão atual), Banco de dados SQL do Azure. |
.gif "Ícone de vínculo de tópico")
Convenções de sintaxe Transact-SQL
Sintaxe
[ 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> ]
[ 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 a porcentagem de linhas aleatórias que serão excluídas.expression pode ser um número ou uma porcentagem das linhas. As linhas referenciadas na expressão TOP usada com INSERT, UPDATE ou DELETE não são organizadas em qualquer ordem. 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.table_alias
O alias especificado na cláusula FROM table_source que representa a tabela ou exibição na qual as linhas devem ser excluídas.server_name
Aplica-se a: do SQL Server 2008 ao SQL Server 2014.
O nome do servidor (que usa 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 de tabela, dentro de seu escopo, também pode ser usada como origem da tabela em uma instrução DELETE.
A exibição referenciada por table_or_view_name deve ser atualizável e fazer referência exata a uma tabela base na cláusula FROM da definição de exibição. Para obter mais informações sobre exibições atualizáveis, consulte CREATE VIEW (Transact-SQL).
rowset_function_limited
Aplica-se a: do SQL Server 2008 ao SQL Server 2014.
A função OPENQUERY ou OPENROWSET, sujeita aos recursos do provedor.
WITH ( <dica_de_tabela_limitada> [... 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 tem 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.
<critério_de_pesquisa>
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 nome cursor_name existirem, esse argumento fará referência ao cursor global se GLOBAL estiver 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 ( <dica_de_consulta> [ ,... n] )
Palavras-chave que indicam as dicas de otimização que são usadas para personalizar a forma como o Mecanismo de Banco de Dados processa a instrução. Para obter mais informações, consulte dicas de consulta (Transact-SQL).
Práticas recomendadas
Para excluir todas as linhas em uma tabela, use TRUNCATE TABLE. TRUNCATE TABLE é mais rápido que DELETE e usa menos recursos do sistema e do log de transações. TRUNCATE TABLE tem restrições; por exemplo, a tabela não pode participar da replicação. Para obter mais informações, consulte TRUNCATE TABLE (Transact-SQL).
Use a função @@ROWCOUNT para retornar o número de linhas excluídas para o aplicativo cliente. Para obter mais informações, consulte @@ROWCOUNT (Transact-SQL).
Tratamento de erros
Você pode implementar o tratamento de erros para a instrução DELETE especificando a instrução em uma construção TRY…CATCH.
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 restante do lote é cancelado e uma mensagem de erro é retornada.
Interoperabilidade
DELETE poderá ser usado no corpo de uma função definida pelo usuário se o objeto modificado for uma variável de tabela.
Ao excluir uma linha que contém uma coluna FILESTREAM, você também excluirá os arquivos subjacentes do sistema de arquivos. Os arquivos subjacentes são removidos pelo coletor de lixo do FILESTREAM. Para obter mais informações, consulte Acessar dados FILESTREAM com Transact-SQL.
A cláusula FROM não pode ser especificada em uma instrução DELETE que faça referência, direta ou indiretamente, a uma exibição com um gatilho INSTEAD OF definido. Para obter mais informações sobre gatilhos INSTEAD OF, consulte CREATE TRIGGER (Transact-SQL).
Limitações e restrições
Quando TOP é usado com DELETE, as linhas referenciadas não são organizadas em ordem alguma e a cláusula ORDER BY não pode ser especificada diretamente nessa instrução. Se você precisar usar TOP para excluir linhas em uma ordem cronológica significativa, será preciso usar TOP junto com uma cláusula ORDER BY em uma instrução de subseleção. Consulte a seção Exemplos a seguir neste tópico.
TOP não pode ser usado em uma DELETE instrução para exibições particionadas.
Comportamento de bloqueio
Por padrão, uma instrução DELETE sempre adquire um bloqueio exclusivo (X) na tabela que modifica e mantém esse bloqueio até que a transação seja concluída. Com um bloqueio exclusivo (X), nenhuma outra transação pode modificar os dados; operações de leitura podem ser realizadas apenas com o uso da dica NOLOCK ou nível de isolamento de leitura não confirmada. Você pode especificar dicas de tabela para substituir esse comportamento padrão durante a instrução DELETE especificando outro método de bloqueio; entretanto, é recomendável que as dicas só sejam usadas como último recurso por desenvolvedores experientes e administradores de bancos de dados. Para obter mais informações, consulte Dicas de tabela (Transact-SQL).
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 exclusivo 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.
Dica
Páginas vazias podem ser removidas de um heap a qualquer momento usando a instrução ALTER TABLE <table_name> REBUILD.
Comportamento de log
A DELETE instrução sempre é registrada em log completamente.
Segurança
Permissões
São necessárias permissões DELETE na tabela de destino. 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. Os membros das funções sysadmin, db_owner e db_securityadmin e o proprietário da tabela podem transferir permissões para outros usuários.
Exemplos
Categoria |
Elementos de sintaxe em destaque |
|---|---|
Sintaxe básica |
DELETE |
Limitando as linhas excluídas |
WHERE • FROM • cursor • |
Excluindo linhas de uma tabela remota |
Servidor vinculado • Função de conjunto de linhas OPENQUERY • Função de conjunto de linhas OPENDATASOURCE |
Capturando os resultados da instrução DELETE |
cláusula OUTPUT |
Sintaxe básica
Os exemplos nesta seção demonstram a funcionalidade básica da instrução DELETE usando a sintaxe mínima necessária.
A. Usando DELETE sem a cláusula WHERE
O exemplo a seguir exclui todas as linhas de uma tabela SalesPersonQuotaHistory no banco de dados AdventureWorks2012 porque uma cláusula WHERE não é usada para limitar o número de linhas excluídas.
DELETE FROM Sales.SalesPersonQuotaHistory;
GO
Limitando as linhas excluídas
Exemplos nesta seção demonstram como limitar o número de linhas que serão excluídas.
A. Usando a cláusula WHERE para excluir um conjunto de linhas
O exemplo a seguir exclui todas as linhas de uma tabela ProductCostHistory no banco de dados AdventureWorks2012 na qual o valor da coluna StandardCost é maior que 1000.00.
DELETE FROM Production.ProductCostHistory
WHERE StandardCost > 1000.00;
GO
O exemplo a seguir mostra uma cláusula WHERE mais complexa. A cláusula WHERE define duas condições que devem ser atendidas para determinar as linhas a serem excluídas. O valor na coluna StandardCost deve ser entre 12.00 e 14.00, e o valor na coluna SellEndDate deve ser nulo. O exemplo também imprime o valor da função @@ROWCOUNT para retornar o número de linhas excluídas.
DELETE Production.ProductCostHistory
WHERE StandardCost BETWEEN 12.00 AND 14.00
AND EndDate IS NULL;
PRINT 'Number of rows deleted is ' + CAST(@@ROWCOUNT as char(3));
B. Usando um cursor para determinar a linha a ser excluída
O exemplo a seguir exclui uma única linha da tabela EmployeePayHistory no banco de dados AdventureWorks2012 que usa um cursor nomeado my_cursor. O operação de exclusão afeta somente a única linha buscada atualmente pelo cursor.
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
C. Usando junções e subconsultas para dados em uma tabela para excluir linhas em outra tabela
Os exemplos a seguir mostram dois modos de excluir linhas em uma tabela com base em dados de outra tabela. Nos dois exemplos, as linhas da tabela SalesPersonQuotaHistory no banco de dados AdventureWorks2012 são excluídas com base nas vendas acumuladas no ano armazenadas na tabela SalesPerson. A primeira instrução DELETE mostra a solução de subconsulta compatível com ISO e a segunda instrução DELETE mostra a extensão FROM Transact-SQL para unir as duas tabelas.
-- SQL-2003 Standard subquery
DELETE FROM Sales.SalesPersonQuotaHistory
WHERE BusinessEntityID IN
(SELECT BusinessEntityID
FROM Sales.SalesPerson
WHERE SalesYTD > 2500000.00);
GO
-- Transact-SQL extension
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
-- Mention target table, SalesPersonQuotaHistory, only once. Simpler.
DELETE spqh
FROM
Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE
sp.SalesYTD > 2500000.00;
A. Usando TOP para limitar o número de linhas excluídas
Quando uma cláusula TOP (n) é usada com DELETE, a operação de exclusão é executada em uma seleção aleatória de um número n de linhas. O exemplo a seguir exclui 20 linhas aleatórias da tabela PurchaseOrderDetail no banco de dados AdventureWorks2012 que têm datas de vencimento anteriores a 1º de julho de 2006.
DELETE TOP (20)
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20020701';
GO
Se você precisar usar TOP para excluir linhas em uma ordem cronológica significativa, será preciso usar TOP junto com ORDER BY em uma instrução de subseleção. A consulta a seguir exclui as 10 linhas da tabela PurchaseOrderDetail que têm as primeiras datas de vencimento. Para garantir que apenas 10 linhas sejam excluídas, a coluna especificada na instrução de subseleção (PurchaseOrderID) é a chave primária da tabela. O uso de uma coluna não chave na instrução de subseleção pode resultar na exclusão de mais de 10 linhas se a coluna especificada contiver valores duplicados.
DELETE FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderDetailID IN
(SELECT TOP 10 PurchaseOrderDetailID
FROM Purchasing.PurchaseOrderDetail
ORDER BY DueDate ASC);
GO
Excluindo linhas de uma tabela remota
Os exemplos nesta seção demonstram como excluir linhas de uma tabela de remota usando um servidor vinculado ou uma função de conjunto de linhas para referenciar a tabela remota. Uma tabela remota existe em um servidor diferente ou em uma instância do SQL Server.
Aplica-se a: do SQL Server 2008 ao SQL Server 2014. |
A. Excluindo dados de uma tabela remota por meio de um servidor vinculado
O exemplo a seguir exclui uma linhas de uma tabela remota. O exemplo começa com a criação de um vínculo com a fonte de dados remota usando sp_addlinkedserver. O nome do servidor vinculado, MyLinkServer, é especificado como parte do nome de objeto de quatro partes no formulário server.catalog.schema.object.
USE master;
GO
-- Create a link to the remote data source.
-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.
EXEC sp_addlinkedserver @server = N'MyLinkServer',
@srvproduct = N' ',
@provider = N'SQLNCLI',
@datasrc = N'server_name',
@catalog = N'AdventureWorks2012';
GO
-- Specify the remote data source using a four-part name
-- in the form linked_server.catalog.schema.object.
DELETE MyLinkServer.AdventureWorks2012.HumanResources.Department WHERE DepartmentID > 16;
GO
B. Excluindo dados de uma tabela remota por meio da função OPENQUERY
O exemplo a seguir exclui linhas de uma tabela remota especificando a função do conjunto de linhas OPENQUERY. O nome de servidor vinculado criado no exemplo anterior é usado neste exemplo.
DELETE OPENQUERY (MyLinkServer, 'SELECT Name, GroupName FROM AdventureWorks2012.HumanResources.Department
WHERE DepartmentID = 18');
GO
C. Excluindo dados de uma tabela remota por meio da função OPENDATASOURCE
O exemplo a seguir exclui linhas de uma tabela remota especificando a função do conjunto de linhas OPENDATASOURCE. Especifique um nome do servidor válido para a fonte de dados com o formato server_name ou server_name\instance_name.
DELETE FROM OPENDATASOURCE('SQLNCLI',
'Data Source= <server_name>; Integrated Security=SSPI')
.AdventureWorks2012.HumanResources.Department
WHERE DepartmentID = 17;'
Capturando os resultados da instrução DELETE
A. 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 no banco de dados AdventureWorks2012.
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
B. Usando OUTPUT INTO com <from_table_name> em uma instrução DELETE
O exemplo a seguir exclui linhas da tabela ProductProductPhoto no banco de dados AdventureWorks2012 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.
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
CREATE TRIGGER (Transact-SQL)
INSERT (Transact-SQL)
SELECT (Transact-SQL)
TRUNCATE TABLE (Transact-SQL)
UPDATE (Transact-SQL)
WITH common_table_expression (Transact-SQL)
@@ROWCOUNT (Transact-SQL)