Início / dba / Perguntas / 134027
Accepted
Greg
Asked: 2016-04-01 21:22:55 +0800 CST

Como SQL Delete usando uma subconsulta

  • 772

O código a seguir foi adicionado por um de nossos desenvolvedores para excluir registros duplicados da tabela:

DELETE  SubQuery

FROM
(
    SELECT  ID
            ,FK1
            ,FK2
            ,CreatedDateTime
            ,ROW_NUMBER() OVER(PARTITION BY FK1, FK2 ORDER BY CreatedDateTime) AS RowNumber

    FROM    Table
)
AS SubQuery

WHERE   RowNumber > 1

Ao revisar o código, presumi que não funcionaria, no entanto, testá-lo em nosso ambiente de teste (SQL 2014) mostra que sim!

Como o SQL sabe resolver a subconsulta e excluir os registros table?

sql-server sql-server-2014

2 respostas

  1. Best Answer

    O subqueryque você tem em seu código é chamado de tabela derivada . Não é uma tabela base, mas uma tabela que "vive" durante o tempo em que a consulta é executada. Como visualizações (que também são chamadas de tabelas visualizadas ) - e em versões recentes CTEs que é outra, 4ª maneira de "definir" uma tabela dentro de uma consulta - elas são semelhantes a uma tabela de várias maneiras. Você pode selecta partir deles, você pode usá-los em fromou para joineles em outras tabelas (base ou não!).

    Em alguns DBMS (nem todos os DBMS implementaram isso da mesma maneira), essas tabelas/visões são atualizáveis . updateE "atualizável" significa que também podemos insertentrar ou deletesair deles.

    No entanto, existem restrições e isso é esperado. Imagine se subqueryfosse uma junção de 2 (ou 17 tabelas). O que deletesignificaria então? (de quais tabelas as linhas devem ser excluídas?) Visualizações atualizáveis ​​é um assunto muito complicado . Existe um livro recente (2012), inteiramente sobre este assunto, escrito por Chris Date, conhecido especialista em teoria relacional: View Updating and Relational Theory .

    Quando a tabela derivada (ou exibição) é uma consulta muito simples, como se tivesse apenas uma tabela base (possivelmente restrita por um WHERE) e nenhum GROUP BY, então cada linha da tabela derivada corresponde a uma linha na tabela base subjacente, então é fácil * para atualizar, inserir ou excluir disso.

    Quando o código dentro da subconsulta é mais complexo, depende se as linhas da tabela/exibição derivada podem ser rastreadas/resolvidas para linhas de uma das tabelas base subjacentes.

    Para SQL Server, você pode ler mais no parágrafo Updatable Views no MSDN: CREATE VIEW.

    Visualizações atualizáveis

    Você pode modificar os dados de uma tabela base subjacente por meio de uma exibição, desde que as seguintes condições sejam verdadeiras:

    • Quaisquer modificações, incluindo UPDATE, INSERTe DELETEinstruções, devem fazer referência a colunas de apenas uma tabela base.

    • As colunas que estão sendo modificadas na exibição devem fazer referência direta aos dados subjacentes nas colunas da tabela. As colunas não podem ser derivadas de nenhuma outra forma, como por meio do seguinte:

    • Uma função agregada: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VARe VARP.

    • Um cálculo. A coluna não pode ser calculada a partir de uma expressão que usa outras colunas. Colunas que são formadas usando os operadores de conjunto UNION, UNION ALL, CROSSJOIN, EXCEPT, e INTERSECT equivalem a um cálculo e também não são atualizáveis.

    • As colunas que estão sendo modificadas não são afetadas por GROUP BY, HAVINGou DISTINCTcláusulas.

    • TOPnão é usado em nenhum lugar no select_statement da exibição junto com a WITH CHECK OPTIONcláusula.

    As restrições anteriores se aplicam a quaisquer subconsultas na FROMcláusula da exibição, assim como se aplicam à própria exibição. Geralmente, o Mecanismo de Banco de Dados deve ser capaz de rastrear modificações de forma inequívoca da definição de exibição para uma tabela base.

    Na verdade deleteé mais fácil, menos complexo do que update. O SQL Server precisa apenas das chaves primárias ou de alguma outra forma de identificar quais linhas da tabela base devem ser excluídas. Para update, há uma restrição adicional (bastante óbvia) de que não podemos atualizar uma coluna computada. Você pode tentar modificar sua consulta para fazer uma atualização. A atualização CreatedDateTimeprovavelmente funcionará bem, mas tentar atualizar a RowNumbercoluna computada gerará um erro. E inserté ainda mais complexo, pois teríamos que fornecer valores para todas as colunas da tabela base que não possuem DEFAULTrestrição.

    • 14

  2. É fácil ver quando você olha para o plano de consulta. No seu caso, o plano contém apenas um operador Segmento e Projeto de Sequência adicional para lidar com o número da linha. Esse tipo de operação só funciona quando o SQL Server realmente pode resolver a tabela subjacente.

    A exclusão de subconsultas e CTEs é totalmente suportada e muito eficiente, especialmente para remover duplicatas. Também me lembro de usá-lo em versões mais antigas do SQL Server.

    Mais em um antigo post meu no blog.

    • 4

relate perguntas