SQL Update-Anweisung mit Join in SQL Server vs Oracle vs PostgreSQL
Von: Andrea Gnemmi | Aktualisiert am: 2021-01-04 | Comments | Related: Mehr > Andere Datenbankplattformen
Problem
Wie die meisten DBAs und Entwickler, die sowohl mit SQL Server als auch mit Oracle arbeiten, bereits wissen, gibt es einige Unterschiede in der Art und Weise, wie man Zeilen mit einem Join zwischen SQL Server und Oracle aktualisiert. Insbesondere ist dies mit Oracle nicht ohne einige Finessen möglich. PostgreSQL hat einen ähnlichen ANSI-SQL-Ansatz wie SQL Server. In diesem Artikel vergleichen wir, wie Aktualisierungen bei Verwendung eines Joins zwischen SQL Server, Oracle und PostgreSQL ausgeführt werden.
Lösung
Nachfolgend werden wir einen Vergleich der unterschiedlichen Syntax für die Aktualisierung von Daten bei Verwendung eines Joins durchführen.
Ein Hinweis zur Terminologie: In Oracle haben wir keine Datenbank namens Chinook, sondern ein Schema oder besser gesagt einen Benutzer. In Oracle können Sie nur eine Datenbank pro Instanz haben, es sei denn, Sie verwenden Pluggable Databases.
Zu Testzwecken werde ich das frei herunterladbare Datenbankbeispiel Chinook verwenden, da es in mehreren RDBMS-Formaten verfügbar ist. Es ist eine Simulation eines digitalen Medienhauses mit einigen Beispieldaten. Alles, was Sie tun müssen, ist, die gewünschte Version herunterzuladen und die Skripte für die Datenstruktur sowie das Einfügen der Daten auszuführen.
SQL Server Update-Anweisung mit Join
Wir beginnen mit einer kurzen Erinnerung daran, wie eine SQL UPDATE-Anweisung mit einem JOIN in SQL Server funktioniert.
Normalerweise aktualisieren wir eine Zeile oder mehrere Zeilen in einer Tabelle mit einem Filter, der auf einerWHERE-Klausel basiert. Wir prüfen auf einen Fehler und stellen fest, dass es die Stadt Vienne nicht gibt. Sondern Wien auf Deutsch oder Vienna auf Englisch:
select customerid, FirstName,LastName, Address, city,countryfrom dbo.Customerwhere city='Vienne'
Wir können dies mit einer normalen UPDATE-Anweisung korrigieren, z. B.:
update dbo.Customerset city='Wien'where city='Vienne'
Wir können auch die Rechnungstabelle basierend auf einem Kunden aktualisieren. Lassen Sie uns zunächst die Daten mit einer einfachen SELECT-Abfrage abfragen:
select invoice.BillingCityfrom invoiceinner join customer on invoice.CustomerId=customer.CustomerIdwhere customer.City='Vienne'
Wir haben dieses Ergebnis:
An dieser Stelle können wir ein UPDATE durchführen, indem wir die gleiche JOIN-Klausel verwenden wie bei der SELECT-Abfrage, die wir gerade durchgeführt haben. Ich weiß, dass wir auch ein einfaches UPDATE für die eine Tabelle hätten verwenden können, aber dieses Beispiel zeigt nur, wie man es mit einem JOIN machen kann:
update invoiceset invoice.BillingCity='Wien'from invoiceinner join customeron invoice.CustomerId=customer.CustomerIdwhere customer.City='Vienne'
Wir können sogar einen CTE (Common Table Expression) in der JOIN-Klausel verwenden, um einen bestimmten Filter zu haben.
Angenommen, wir müssen einen speziellen Rabatt auf die Gesamtrechnung für österreichische Kunden geben, die mehr als 20 Dollar für Rock und Metal (Genre 1 und 3) ausgegeben haben. Die Teilmenge lässt sich leicht mit der folgenden Abfrage extrahieren:
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
Angenommen, wir wollen einen Rabatt von 20 % anwenden. Wir können ihn anwenden, indem wir die Gesamtrechnungstabelle basierend auf der obigen Abfrage mit dem folgenden CTE aktualisieren:
; 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
Oracle Update Statement with Join
Wie funktioniert das in Oracle? Die Antwort ist ziemlich einfach: In Oracle wird diese Syntax derUPDATE -Anweisung mit einem JOIN nicht unterstützt.
Wir müssen einige Abkürzungen nehmen, um etwas Ähnliches zu erreichen. Wir können eine Subquery und einen IN-Filter verwenden. Zum Beispiel können wir das erste UPDATE mit dem JOIN umwandeln, das wir in SQL Server verwendet haben.
Zunächst wollen wir mit der gleichen SELECT-Abfrage die Daten der Tabelle Invoice in Oracle überprüfen:
select invoice.BillingCityfrom chinook.invoiceinner join chinook.customer on invoice.CustomerId=customer.CustomerIdwhere customer.City='Vienne';
Wir werden die UPDATE-Anweisung mit der obigen Abfrage als Subquery transformieren, aber wir werden den Primärschlüssel Invoiceid extrahieren, um das Update durchzuführen:
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' );
Vergessen Sie nicht das Commit. Da wir uns in Oracle befinden, gibt es standardmäßig kein Auto-Commit:
commit;
Das war recht einfach, aber nehmen wir an, dass wir ein UPDATE auf Basis einer anderen großen Tabelle durchführen und den Wert in der anderen Tabelle verwenden müssen.
Angenommen, wir möchten das gleiche UPDATE durchführen, wie wir es mit dem CTE auf dem SQL Server gemacht haben, können wir das JOIN-Problem mit diesem Code überwinden:
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;
Wie Sie vielleicht bemerkt haben, habe ich die CTE in eine Subquery umgewandelt und mit der Rechnungstabelle verbunden, ähnlich wie beim Update mit SQL Server. Aber diesmal ist es eine Select-Anweisung mit dem Primärschlüssel und der Summe, die wir aktualisieren möchten. Ich habe dieses Ergebnis als die zu aktualisierende Tabelle eingegeben. Es ist ein Workaround, aber es funktioniert! Das Einzige, worauf Sie achten müssen, ist sicherzustellen, dass die Ergebnisse eindeutig sind und Sie nicht versuchen, mehr Zeilen zu aktualisieren, als Sie benötigen. Deshalb mache ich immer vorher einselect, um zu prüfen, wie viele Zeilen aktualisiert werden sollen.
Es gibt eine elegantere Lösung, die die MERGE-Anweisung verwendet. Die Syntax ist ähnlich wie bei MERGE in SQL Server, also verwenden wir das gleiche UPDATE wie im letzten Beispiel.
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;
Wie Sie sehen, ist es lesbarer als die vorherige Lösung. In SQL Server ist die Leistung von MERGE-Anweisungen nicht immer die beste. Beachten Sie dies bei der Verwendung und testen Sie es, bevor Sie es in einer Produktionsumgebung einsetzen.
PostgreSQL Update-Anweisung mit Join
Was ist mit PostgreSQL? In diesem Fall funktionieren die gleichen Konzepte, die in SQL Server funktionieren, auch bei PostgreSQL. Wir haben nur ein paar Unterschiede in der Syntax, da wir den Join nicht angeben. Dafür verwenden wir die alte Join-Syntax mit der WHERE-Klausel.
Lassen Sie uns denselben SQL-Code, den wir auf SQL Server verwendet haben, anpassen und ihn auf der Chinook-Datenbank auf PostgreSQL testen:
update "Invoice"set "BillingCity"='Wien'from "Customer"where "Invoice"."CustomerId"="Customer"."CustomerId"and "Customer"."City"='Vienne'
In diesem Fall müssen wir die erste Tabelle, auf der wir das Update durchführen, nicht angeben. Der Rest ist genau dasselbe wie in SQL Server.
Lassen Sie uns den Code mit der CTE testen (bitte beachten Sie, dass wir in PostgreSQL alle Spaltennamen, die mit einem Großbuchstaben erstellt wurden, unter Anführungszeichen setzen müssen, da sie sonst nicht erkannt werden!):
; 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"
Zusammenfassung
In diesem Tipp haben wir die Syntaxunterschiede für UPDATE-Anweisungen bei Verwendung eines JOIN in SQL Server, Oracle und PostgreSQL gesehen.
Nächste Schritte
- Es gibt noch andere Möglichkeiten, mit Joins in Oracle zu aktualisieren, insbesondere die Verwendung derWHERE EXISTS-Klausel mit Unterabfragen. Aber es ist ähnlich wie mit der Syntax, die ich in meinem Codebeispiel verwendet habe.
- Bitte beachten Sie, dass ich SSMS für SQL Server, SQL Developer für Oracle und PGAdmin für PostgreSQL verwendet habe. Alle stehen kostenlos zum Download zur Verfügung.
Last Updated: 2021-01-04
Über den Autor
Andrea Gnemmi ist ein Datenbank- und Data Warehouse-Profi mit fast 20 Jahren Erfahrung, und hat seine Karriere in der Datenbankadministration mit SQL Server 2000 begonnen.Alle meine Tipps ansehen
- Mehr SQL Server DBA Tipps…