Sentencia de actualización SQL con Join en SQL Server vs Oracle vs PostgreSQL

Por: Andrea Gnemmi | Actualizado: 2021-01-04 | Comentarios | Relacionados: Más > Otras Plataformas de Bases de Datos

Problema

Como la mayoría de los DBA’s y desarrolladores que trabajan tanto con SQL Server como con Oracleya saben, hay algunas diferencias en la forma de actualizar filas usando un joinentre SQL Server y Oracle. En particular, esto no es posible con Oracle sin cierta delicadeza. PostgreSQL tiene un enfoque ANSI SQL similar al de SQL Server. En este artículo comparamos cómo ejecutar actualizaciones cuando se utiliza un join entre SQL Server, Oracle y PostgreSQL.

Solución

A continuación haremos una comparación de las diferentes sintaxis utilizadas para actualizar datoscuando se utiliza un join.

Una nota respecto a la terminología: en Oracle no tendremos una base de datos llamada Chinook, sino un esquema o más bien un Usuario. En Oracle, a no ser que se utilicen bases de datos enchufables, sólo se puede tener una base de datos por instancia.

Para las pruebas utilizaré la base de datos descargable gratuitamente Chinook, ya que está disponible en múltiples formatos RDBMS. Es una simulación de una tienda de medios digitales con algunos datos de muestra. Todo lo que tiene que hacer es descargar la versión que necesita y ejecutar los scripts para la estructura de datos, así como la inserción de los datos.

Sentencia SQL Server Update con Join

Empezaremos con un rápido recordatorio de cómo funciona una sentencia SQL UPDATE con un JOIN en SQL Server.

Normalmente actualizamos una fila o varias filas de una tabla con un filtro basado en una cláusulaWHERE. Comprobamos si hay un error y encontramos que no existe la ciudad de Vienne. Sino, Wien en alemán o Viena en inglés:

select customerid, FirstName,LastName, Address, city,countryfrom dbo.Customerwhere city='Vienne'

resultados de la consulta al servidor de sql

Podemos corregirlo con una sentencia UPDATE normal como:

update dbo.Customerset city='Wien'where city='Vienne'

También podemos actualizar la tabla de facturas en base a un cliente. En primer lugar, vamos a revisar los datos con una simple consulta SELECT:

select invoice.BillingCityfrom invoiceinner join customer on invoice.CustomerId=customer.CustomerIdwhere customer.City='Vienne'

Tenemos este resultado:

resultado de la consulta al servidor de sql

En este punto podemos hacer un UPDATE usando la misma cláusula JOIN que la consulta SELECT que acabamos de hacer. Sé que podríamos haber utilizado un simple UPDATE a la tabla única, pero este ejemplo sólo muestra cómo se puede hacer al hacer un JOIN:

update invoiceset invoice.BillingCity='Wien'from invoiceinner join customeron invoice.CustomerId=customer.CustomerIdwhere customer.City='Vienne'

Incluso podemos utilizar una CTE (Common Table Expression) en la cláusula JOIN para tener algún filtro en particular.

Por ejemplo, supongamos que necesitamos dar un descuento especial en el total de la factura a los clientes austriacos que gastaron más de 20 dólares en Rock y Metal (género 1 y 3). El subconjunto se extrae fácilmente con la siguiente 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

resultados de la consulta en el servidor de SQL

Supongamos que queremos aplicar un descuento del 20%. Podemos aplicarlo actualizando el total de la tabla de facturas en base a la consulta anterior utilizando el siguiente 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

Declaración de actualización de Oracle con Join

¿Cómo funciona esto en Oracle? La respuesta es bastante sencilla: en Oracle no se admite esta sintaxis de sentenciaUPDATE con un JOIN.

Debemos hacer algunos atajos para poder hacer algo similar. Podemos hacer uso de una subconsulta y un filtro IN. Por ejemplo, podemos transformar el primer UPDATE con el JOIN que utilizamos en SQL Server.

Primero, vamos a comprobar con la misma consulta SELECT los datos de la tabla Invoice en Oracle:

select invoice.BillingCityfrom chinook.invoiceinner join chinook.customer on invoice.CustomerId=customer.CustomerIdwhere customer.City='Vienne';

Transformaremos la sentencia UPDATE utilizando la consulta anterior como subconsulta, pero extraeremos la clave primaria Invoiceid para poder hacer la actualización:

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' );

No olvides hacer el commit. Como estamos en Oracle, no hay auto commit por defecto:

commit;

Eso fue bastante fácil, pero supongamos que necesitamos hacer un UPDATE basado en otra tabla grande y usar el valor de la otra tabla.

Supongamos que queremos hacer el mismo UPDATE que hicimos con el CTE en SQL Server, podemos superar el problema del JOIN con 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 habrás podido comprobar, he transformado el CTE en una subconsulta y lo he unido con la tabla de Facturas de forma similar a la actualización realizada con SQL Server. Pero esta vez es una sentencia select con la clave primaria y el total que queremos actualizar. He puesto este resultado como la tabla a actualizar. Es una solución, pero funciona. Lo único que hay que tener en cuenta es que los resultados sean únicos y que no se intenten actualizar más filas de las que se necesitan. Por eso siempre hago un aselect antes para comprobar cuántas filas deben ser actualizadas.

Hay una solución más elegante, haciendo uso de la sentencia MERGE. La sintaxis es similar a la de MERGE en SQL Server por lo que utilizaremos el mismo UPDATE que en el último ejemplo.Esto debería escribirse así:

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 puedes ver, es más legible que la solución anterior. En SQL Server, el rendimiento de las sentencias MERGE no siempre es el mejor. Téngalo en cuenta a la hora de utilizarla y pruébela antes de usarla en un entorno de producción.

Sentencia de actualización PostgreSQL con Join

¿Qué pasa con PostgreSQL? En este caso, los mismos conceptos que funcionan en SQL Serverhacen el trabajo también en PostgreSQL. Sólo tenemos algunas diferencias con la sintaxis ya que no especificamos el join. Pero utilizamos la antigua sintaxis de join con la cláusula WHERE.

Adaptemos el mismo código SQL que hemos utilizado en SQL Server y probémoslo en la base de datos Chinook en PostgreSQL:

update "Invoice"set "BillingCity"='Wien'from "Customer"where "Invoice"."CustomerId"="Customer"."CustomerId"and "Customer"."City"='Vienne'

En este caso no necesitamos especificar la primera tabla sobre la que vamos a realizar la actualización. El resto es exactamente igual que en SQL Server.

Probemos el código con el CTE (tened en cuenta que en PostgreSQL tenemos que poner todos los nombres de las columnas que se han creado con mayúsculas entre comillas¡de lo contrario no los reconocerá!):

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

Resumen

En este consejo hemos visto las diferencias de sintaxis para las sentencias UPDATE al utilizar un JOIN en SQL Server, Oracle y PostgreSQL.

Siguientes pasos

Hay otras formas de actualizar con joins en Oracle, especialmente utilizando la cláusulaWHERE EXISTS con subconsultas. Pero es similar a lo que se logró utilizando la sintaxis que he empleado en mi código de ejemplo.
Tenga en cuenta que he utilizado SSMS para SQL Server, SQL Developer para Oracley PGAdmin para PostgreSQL. Todos están disponibles para su descarga gratuita.

Última actualización: 2021-01-04

Acerca del autor

Andrea Gnemmi es una profesional de Bases de Datos y Data Warehouse con casi 20 años de experiencia, habiendo comenzado su carrera en la Administración de Bases de Datos con SQL Server 2000.
Ver todos mis consejos

Recursos relacionados

Más consejos de DBA de SQL Server…