Declaração de Actualização SQL com Join in SQL Server vs Oracle vs PostgreSQL
Por Andrea Gnemmi | Actualizado: 2021-01-04 | Comentários | Related: Mais > Outras Plataformas de Base de Dados
Problema
Como a maioria dos DBA’s e programadores que trabalham tanto com o SQL Server como com o Oracle já sabem, existem algumas diferenças na forma como se actualizam as linhas usando um joinbet entre o SQL Server e o Oracle. Notavelmente, isto não é possível com Oracle com uma grande fineza. O PostgreSQL tem uma abordagem ANSI SQL semelhante à do SQL Server. Neste artigo comparamos como executar actualizações quando se usa um join entre SQL Server, Oracle e PostgreSQL.
Solução
Below faremos uma comparação da diferente sintaxe usada para actualizar dados quando se usa um join.
Uma nota sobre a terminologia: em Oracle não teremos uma base de dados chamada Chinook, mas um esquema ou mais propriamente um Utilizador. Em Oracle, a menos que utilize Bases de Dados Plugáveis, poderá ter apenas uma base de dados por instância.
Para fins de teste, utilizarei as bases de dados descarregáveis gratuitas amostra de Chinook, uma vez que está disponível em múltiplos formatos RDBMS. É uma simulação de uma loja de meios digitais com dados de amostras. Tudo o que tem de fazer é descarregar a versão de que necessita e executar os scripts para a estrutura de dados, bem como inserir os dados.
SQL Server Update Statement with Join
Comecemos com um rápido lembrete de como funciona uma instrução SQL UPDATE com um JOIN SQL Server.
Normalmente, actualizamos uma linha ou várias linhas numa tabela com um filtro baseado na AQUI. Verificamos a existência de um erro e descobrimos que não existe uma cidade como Vienne. Butrather, Wien em alemão ou Viena em inglês:
select customerid, FirstName,LastName, Address, city,countryfrom dbo.Customerwhere city='Vienne'
Podemos corrigir isto com uma declaração normal de UPDATE, como por exemplo:
update dbo.Customerset city='Wien'where city='Vienne'
Podemos também actualizar a tabela de facturação com base num cliente. Primeiro, vamos esquematizar os dados com uma simples consulta SELECT:
select invoice.BillingCityfrom invoiceinner join customer on invoice.CustomerId=customer.CustomerIdwhere customer.City='Vienne'
Temos este resultado:
Neste ponto podemos fazer uma UPDATE usando a mesma cláusula JOIN que a consulta SELECT que acabámos de fazer. Sei que poderíamos ter usado uma simples ACTUALIZAÇÃO à única tabela, mas este exemplo mostra apenas como pode ser feito quando se faz uma JUNTA:
update invoiceset invoice.BillingCity='Wien'from invoiceinner join customeron invoice.CustomerId=customer.CustomerIdwhere customer.City='Vienne'
Podemos até usar um CTE (Common Table Expression) na cláusula JOIN a fim de ter algum filtro específico.
Por exemplo, suponha que precisamos de dar um desconto especial no total da factura para clientes austríacos que gastaram mais de 20 dólares em Rock e Metal(géneros 1 e 3). O subconjunto é facilmente extraído com a seguinte consulta:
select Invoice.CustomerId,sum(invoiceline.UnitPrice*Quantity) as genretotalfrom invoiceinner join InvoiceLine on Invoice.InvoiceId=InvoiceLine.InvoiceIdinner join Track on Track.TrackId=InvoiceLine.TrackIdinner join customer on customer.CustomerId=Invoice.CustomerIdwhere country='Austria' and GenreId in (1,3)group by Invoice.CustomerIdhaving sum(invoiceline.UnitPrice*Quantity)>20
P>Ponhamos que queremos aplicar um desconto de 20%. Podemos aplicá-lo actualizando a tabela de facturação total com base na consulta acima usando o CTE:
; with discount as( select Invoice.CustomerId, sum(invoiceline.UnitPrice*Quantity) as genretotal from invoice inner join InvoiceLine on Invoice.InvoiceId=InvoiceLine.InvoiceId inner join Track on Track.TrackId=InvoiceLine.TrackId inner join customer on customer.CustomerId=Invoice.CustomerId where country='Austria' and GenreId in (1,3) group by Invoice.CustomerId having sum(invoiceline.UnitPrice*Quantity)>20)update Invoiceset total=total*0.8from Invoiceinner join discount on Invoice.CustomerId=discount.CustomerId
>div>>
sql resultados da consulta do servidor
Oracle Update Statement with Join
Como é que isto funciona no Oracle? A resposta é bastante simples: em Oracle esta sintaxe de declaraçãoUPDATE com um JOIN não é suportada.
Temos de fazer alguns atalhos de modo a fazer algo semelhante. Podemos fazer uso de uma subconsulta e de um filtro IN. Por exemplo, podemos transformar a primeira UPDATE com o JOIN que utilizámos no SQL Server.
P>Primeiro, vamos verificar com o mesmo SELECT consultar os dados da tabela de Facturas no Oracle:
select invoice.BillingCityfrom chinook.invoiceinner join chinook.customer on invoice.CustomerId=customer.CustomerIdwhere customer.City='Vienne';
Transformaremos a declaração UPDATE usando a consulta acima como subconsulta, mas extrairemos a chave primária Invoiceid a fim de fazer a actualização:
update chinook.invoiceset invoice.BillingCity='Wien'where invoiceid in ( select invoiceid from chinook.invoice inner join chinook.customer on invoice.CustomerId=customer.CustomerId where customer.City='Vienne' );
Não se esqueça de se comprometer. Uma vez que estamos em Oracle, não há auto commitby default:
commit;
Isso foi bastante fácil, mas suponhamos que precisamos de uma UPDATE baseada numa outra grande tabela e usar o valor na outra tabela.
Se quisermos fazer o mesmo UPDATE como fizemos com o CTE no SQL Server, podemos ultrapassar o problema do JOIN com este código:
update ( select invoice.customerid,total from chinook.Invoice inner join ( select Invoice.Cust merId as customerid_sub, sum(invoiceline.UnitPrice*Quantity) as genretotal from chinook.invoice inner join chinook.InvoiceLine on Invoice.InvoiceId=InvoiceLine.InvoiceId inner join chinook.Track on Track.TrackId=InvoiceLine.TrackId inner join chinook.customer on customer.CustomerId=Invoice.CustomerId where country='Austria' and GenreId in (1,3) group by Invoice.CustomerId having sum(invoiceline.UnitPrice*Quantity)>20 ) on Invoice.CustomerId=Customerid_sub )set total=total*0.8;
commit;
Como deve ter notado, transformei o CTE numa subconsulta e juntei-o com a tabela Invoice semelhante à actualização feita com o SQL Server. Mas desta vez trata-se de uma declaração seleccionada com a chave primária e o total que gostaríamos de actualizar. Veput este resultado como a tabela a actualizar. É uma alternativa, mas funciona! Só é preciso ter cuidado para garantir que os resultados são únicos e não se está a tentar actualizar mais linhas do que as que se precisam. É por isso que faço sempre uma selecção prévia para verificar quantas linhas devem ser actualizadas.
Existe uma solução mais elegante, fazendo uso da declaração MERGE. A sintaxe semelhante à do MERGE no SQL Server, pelo que utilizaremos a mesma UPDATE como no último exemplo. Isto deve ser escrito assim:
MERGE INTO chinook.InvoiceUSING ( select Invoice.CustomerId as customerid_sub, sum(invoiceline.UnitPrice*Quantity) as genretotal from chinook.invoice inner join chinook.InvoiceLine on Invoice.InvoiceId=InvoiceLine.InvoiceId inner join chinook.Track on Track.TrackId=InvoiceLine.TrackId inner join chinook.customer on customer.CustomerId=Invoice.CustomerId where country='Austria' and GenreId in (1,3) group by Invoice.CustomerId having sum(invoiceline.UnitPrice*Quantity)>20 ) ON (Invoice.CustomerId=Customerid_sub)WHEN MATCHED THENUPDATE set total=total*0.8;
Como pode ver, é mais legível do que a solução anterior. No SQL Server,o desempenho das declarações MERGE nem sempre é o melhor. Tenha isto em mente ao utilizá-lo e testá-lo antes de o utilizar num ambiente de produção.
PostgreSQL Update Statement with Join
E que dizer do PostgreSQL? Neste caso, os mesmos conceitos que funcionam no SQL Serverdo o trabalho também no PostgreSQL. Temos apenas algumas diferenças com a sintaxe, uma vez que não especificamos o join. Mas utilizamos a velha sintaxe de join com o WHEREclause.
Vamos adaptar o mesmo código SQL que utilizámos no SQL Server e testit na base de dados Chinook no PostgreSQL:
update "Invoice"set "BillingCity"='Wien'from "Customer"where "Invoice"."CustomerId"="Customer"."CustomerId"and "Customer"."City"='Vienne'
Neste caso, não precisamos de especificar a primeira tabela em que faremos a actualização. O resto é exactamente o mesmo que no SQL Server.
P>Pomos à prova o código com o CTE (note que no PostgreSQL precisamos de colocar todos os nomes de coluna que foram criados com uma letra maiúscula sob aspas, caso contrário não os reconhecerá!):
; with discount as( select "Invoice"."CustomerId", sum("InvoiceLine"."UnitPrice"*"Quantity") as genretotal from "Invoice" inner join "InvoiceLine" on "Invoice"."InvoiceId"="InvoiceLine"."InvoiceId" inner join "Track" on "Track"."TrackId"="InvoiceLine"."TrackId" inner join "Customer" on "Customer"."CustomerId"="Invoice"."CustomerId" where "Country"='Austria' and "GenreId" in (1,3) group by "Invoice"."CustomerId" having sum("InvoiceLine"."UnitPrice"*"Quantity")>20 )update "Invoice"set "Total"="Total"*0.8from discountwhere "Invoice"."CustomerId"=discount."CustomerId"
Resumo
Nesta dica vimos as diferenças de sintaxe para as declarações UPDATE ao utilizar um JOIN no SQL Server, Oracle e PostgreSQL.
Passos seguintes
- Existem outras formas de actualização com as joint-ins no Oracle, nomeadamente utilizando a cláusulaWHERE EXISTS com subconsultas. Mas é semelhante ao que foi conseguido utilizando a sintaxe que utilizei no meu exemplo de código.
li>Por favor note que utilizei SSMS para SQL Server, SQL Developer para Oracle e PGAdmin para PostgreSQL. Todos estão disponíveis para descarregar gratuitamente.
br>Lest Updated: 2021-01-04
h5>Sobre o author
Andrea Gnemmi é uma profissional de Base de Dados e Data Warehouse com quase 20 anos de experiência, tendo iniciado a sua carreira na administração de bases de dados com o SQL Server 2000.Ver todas as minhas dicas
ul>>li>Mais Dicas DBA Server SQL…
br>