Como usar subconsultas nas declarações INSERT, UPDATE, e DELETE

Você sabia que as subconsultas também podem ser usadas nas declarações UPDATE, INSERT, e DELETE? As subconsultas em SQL são como blocos de construção que podem ser usados em uma variedade de instruções, não apenas SELECT. Se você achar as subconsultas úteis no SELECT, leia este artigo para descobrir como elas podem ser úteis em outras instruções. Você será capaz de criar consultas SQL muito mais complexas e poderosas num piscar de olhos!

Se você quiser atualizar seus conhecimentos sobre subconsultas, recomendo a tabela interativa Curso de Práticas em SQL curso. Ele contém mais de 80 exercícios sobre subconsultas e outras construções desafiadoras SELECT.

Ponto de partida: Uma rápida revisão das subconsultas

Vamos começar com um rápido lembrete do que é uma subquisição. Como as subconsultas são usadas com mais freqüência em declarações SELECT, vamos rever um exemplo de uma simples subconsulta em uma declaração SELECT.

Podemos definir uma subconsulta como uma consulta dentro de outra consulta. Embora subconsultas sejam usadas com mais freqüência na cláusula WHERE das declarações SELECT, elas podem ser usadas em várias outras cláusulas, incluindo WHERE, FROM, e HAVING, entre outras.

Vejamos o banco de dados que vamos usar como exemplo. Imagine que você é o proprietário de uma loja de vinhos, e tem um banco de dados simples com 3 tabelas para administrar a operação da loja.

A primeira tabela é wineque armazena os produtos que você vende, com o nome, o preço, o número de garrafas em estoque, etc. para cada vinho. A segunda é orderA empresa é uma empresa que armazena os pedidos que recebemos de nossos clientes, incluindo o nome do vinho pedido e a quantidade pedida, entre outras informações.

wine

order

Imagine que queremos obter uma lista de vinhos para os quais nunca recebemos um pedido. A consulta será parecida com esta:

SELECT  name,
FROM    wine
WHERE   name NOT IN ( SELECT wine_name FROM order )

A subconsulta devolve os nomes de todos os vinhos para os quais recebemos pedidos. Em seguida, a consulta externa, utilizando o operador NOT IN, obtém os nomes dos vinhos nunca incluídos em nenhum pedido. SQL é tão simples quanto poderoso! Se você quiser aperfeiçoar alguns conceitos de subconsulta, sugiro o curso SQL para Iniciantes onde você pode encontrar uma seção completa sobre subconsultas.

Primeira parada: Uso de Subconsultas nas Declarações INSERT

Vamos agora usar uma subconsulta em uma declaração em INSERT. Isto é muito comum; a idéia é inserir o resultado completo de uma subconsulta ou uma declaração SELECT em uma tabela. Como exemplo, imagine que queremos criar faturas para todas as encomendas de vinho que recebemos durante o dia. Abaixo está uma visão parcial do nosso invoice mesa:

Finja que a data de hoje é 28 de junho de 2020, e queremos inserir os registros das faturas associadas aos pedidos de hoje. Podemos usar o seguinte SELECT para gerar os dados para as faturas:

SELECT  o.date, 
        o.client_id, 
        o.quantity * w.price as amount, 
        o.quantity || ’ bottles of ‘ || o.wine_name,
        o.order_id
FROM    order o 
JOIN    wine w ON w.name = o.wine_name
WHERE   o.date = ‘2020-06-28’

O resultado desta consulta está abaixo:

Isto é exatamente o que queremos inserir na tabela invoice.

Se simplesmente adicionarmos uma cláusula INSERT antes da consulta, podemos inserir o resultado da consulta na tabela winecomo podemos ver no exemplo a seguir:

INSERT INTO invoice (date, client_id, amount, wine_name, order_id)
SELECT  o.date, 
        o.client_id, 
        o.quantity * w.price as amount, 
        o.quantity || ’ bottles of ‘ || o.wine_name,
        o.order_id
FROM    order o 
JOIN    wine w ON w.name = o.wine_name
WHERE   o.date = ‘2020-06-28’

Observe o seguinte:

• Precisamos nomear explicitamente as colunas do invoice tabela na qual estamos inserindo.
• As colunas da lista SELECT devem estar na mesma ordem que as colunas da tabela.
• Omitimos a coluna invoice_id para permitir que o banco de dados escolha o próximo valor usando um gerador de seqüências por padrão.

Após a execução do INSERT, a tabela invoice terá os novos registros de faturas para os pedidos de hoje. Podemos ver isso abaixo, com os novos registros em vermelho:

Suponha que em um determinado dia, nós acidentalmente executamos o INSERT duas vezes, e como resultado, nossos clientes receberam duas faturas para cada pedido. Não queremos repetir o mesmo erro no futuro! Para evitar isso, adicionamos uma subconsulta à declaração INSERT para ver se já existe uma fatura com o mesmo order_id. A seguir, a nova versão da consulta INSERT. A subconsulta adicionada no final identifica as faturas já existentes, e a consulta externa as descarta utilizando o operador NOT IN.

INSERT INTO invoice (date, client_id, amount, wine_name, order_id)
SELECT order.date, 
       order.client_id, 
       o.quantity * w.price as amount, 
       o.quantity || ’ bottles of ‘ || o.wine_name,
       o.order_id
FROM   order o 
JOIN   wine ON wine.name = order.wine_name
WHERE  order.date = ‘2020-06-28’
  AND  o.order_id NOT IN (SELECT order_id 
                           FROM invoice i 
                           WHERE i.order_id=o.order_id )

Se você quiser melhorar suas habilidades no uso de INSERT, UPDATE, e DELETE declarações, sugiro o curso Usando Comandos INSERT, UPDATE e DELETE em SQL onde você pode ver vários exemplos destas declarações.

Segunda parada: Subconsultas em declarações UPDATE

Como SELECT, a declaração UPDATE pode ter uma subconsulta em vários lugares ou cláusulas. Em um UPDATE, as duas cláusulas em que as subconsultas são mais comumente utilizadas são SET e WHERE.

A cláusula SET é onde definimos o novo valor para a coluna sendo modificada pelo UPDATE. Podemos usar uma subconsulta para obter este novo valor, que pode ser selecionado de qualquer tabela ou qualquer subconsulta válida, desde que retornemos apenas um registro com apenas uma coluna para cada registro sendo atualizado. O tipo de dados da coluna devolvida pela subconsulta deve ser do mesmo tipo que a coluna que está sendo modificada.

Vamos criar um UPDATE para manter nosso estoque de garrafas de vinho em dia. No final do dia, vamos criar um UPDATE para refletir o que vendemos hoje. O código terá este aspecto:

UPDATE wine w
SET stock = stock - (
                     SELECT SUM (quantity)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )

Se não usarmos uma cláusula WHERE no UPDATE, acabaremos modificando todos os registros no vinho de mesa, incluindo os registros dos vinhos que não vendemos hoje. A subconsulta devolve um NULL para qualquer vinho não vendido hoje, e nós erroneamente SET a coluna de estoque para NULL, já que o resultado da expressão "stock - NULL" é NULL. Precisamos consertar isso.

Há duas abordagens para o conserto. A primeira é modificar a expressão SUM(quantity) para devolver um zero em vez de um NULL. Para isso, precisamos simplesmente usar a função COALESCE, assim:

UPDATE wine w
SET stock = stock - (
                     SELECT coalesce(SUM (quantity), 0)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )

A segunda abordagem é acrescentar uma subconsulta na cláusula WHERE para modificar somente os vinhos que foram encomendados hoje e manter a expressão SUM(quantity) como está. A consulta a seguir mostra esta abordagem:

UPDATE wine w
SET stock = stock - (
                     SELECT SUM (quantity)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )
WHERE w.name IN (SELECT order.wine_name FROM order WHERE date = CURRENT_DATE)

Esta UPDATE não é ideal: esta cláusula SET utiliza uma subconsulta correlata. Uma subconsulta correlata é aquela que o banco de dados precisa executar muitas vezes - uma vez para cada linha sendo modificada na tabela. Em nosso exemplo, a consulta obtém o SUM(quantity) para cada vinho vendido hoje. Embora as subconsultas correlacionadas possam ser poderosas, elas são melhor evitadas sempre que possível como uma questão de melhor prática. Aqui, podemos evitar a subconsulta correlata usando uma cláusula FROM no UPDATE, como podemos ver abaixo:

UPDATE wine w
SET stock = stock - subquery.total_in_orders
FROM ( SELECT wine_name, sum( order_quantity ) AS total_in_orders  
       FROM   order 
       WHERE  date = TODAY 
       GROUP BY wine_name 
     ) subquery
WHERE w.name = subquery.wine_name 

Como recurso suplementar, sugiro o artigo Beginners Guide to the SQL Subquery, onde você pode aprender o ABC das subconsultas através de exemplos de complexidades variadas.

Terceira parada: Subconsultas nas Declarações DELETE

Com a declaração DELETE, subconsultas podem ser usadas somente dentro de uma cláusula WHERE. Digamos que queremos eliminar os registros de vinhos para os quais não recebemos nenhuma encomenda nos últimos 6 meses. Podemos criar uma subconsulta que devolva os vinhos vendidos nos últimos 6 meses e depois identificar os registros que queremos remover no wine utilizando um operador NOT IN. Vamos ver como o SQL faz isso:

DELETE FROM wine w
WHERE name NOT IN ( SELECT wine_name 
                    FROM   order 
                    WHERE  date >= CURRENT_DATE - interval ‘6 Months’ 
                  )

Agora, suponhamos que queremos eliminar os vinhos para os quais o total de pedidos nos últimos 6 meses foi inferior a 10 unidades. A declaração DELETE terá este aspecto:

DELETE FROM wine w
WHERE 10 > ( SELECT SUM(quantity) 
                    FROM   order o 
                    WHERE  o.wine_name = w.name 
                      AND  date >= CURRENT_DATE - interval ‘6 Months’
                  )

Aqui, a subconsulta devolve a quantidade de garrafas encomendadas nos últimos 6 meses para cada vinho. Comparando esta quantidade com 10, podemos determinar se um determinado vinho deve ser eliminado.

Chegando ao final da subconsulta

As subconsultas são como blocos de construção em SQL. Vimos como eles podem ser usados em vários lugares, como em declarações SELECT ou em qualquer declaração de modificação como INSERT, UPDATE, e DELETE. Vimos como poderíamos usar uma subconsulta em diferentes cláusulas e que havia diferentes maneiras de usar subconsultas em instruções INSERT, UPDATE, e DELETE. Se você quiser saber mais sobre subconsultas, sugiro o artigo Subquery vs. CTE: A SQL Primer, no qual um tipo de subconsulta chamado CTE é explicado com muitos exemplos e detalhes. Finalmente, há dois cursos com muitas informações úteis sobre subconsultas com muitos exemplos: SQL para Iniciantes onde você encontra uma seção completa sobre subconsultas, e Usando Comandos INSERT, UPDATE e DELETE em SQL que é um curso mais avançado. Vamos melhorar suas habilidades SQL!