SQL ServerとOracleとPostgreSQLにおけるJoinを使用したSQLアップデートステートメント
By: Andrea Gnemmi｜更新日時 2021-01-04｜コメント｜関連 その他 > 他のデータベースプラットフォーム
問題
SQL ServerとOracleの両方を扱うDBAや開発者のほとんどがすでに知っているように、SQL ServerとOracleではJoinを使用して行を更新する方法にいくつかの違いがあります。 特に、Oracleでは多少の工夫をしないとこれはできません。 PostgreSQLは、SQL Serverと同様のANSI SQLアプローチを採用しています。
解決策
以下では、結合を使用してデータを更新するために使用される異なる構文を比較します。
用語に関する注意：Oracleでは、Chinookという名前のデータベースではなく、スキーマ、より正確にはユーザーがあります。
テストのために、無料でダウンロードできるデータベース サンプルの Chinook を使用します。 これは、デジタル メディア ストアのシミュレーションで、いくつかのサンプル データがあります。
SQL Server Update Statement with Join
まず、SQL Server で JOIN を使用した SQL UPDATE ステートメントがどのように機能するかを簡単に思い出してみましょう。
通常、WHERE句に基づいたフィルタを使用して、テーブルの行または複数の行を更新します。 エラーをチェックすると、Vienneという都市は存在しないことがわかります。
select customerid, FirstName,LastName, Address, city,countryfrom dbo.Customerwhere city='Vienne'
次のような通常のUPDATEステートメントで修正することができます。
update dbo.Customerset city='Wien'where city='Vienne'
顧客に基づいて請求書テーブルを更新することもできます。 まず、単純な SELECT クエリでデータをチェックしてみましょう:
select invoice.BillingCityfrom invoiceinner join customer on invoice.CustomerId=customer.CustomerIdwhere customer.City='Vienne'
このような結果が得られました。
この時点で、先ほどの SELECT クエリと同じ JOIN 句を使用して UPDATE を行うことができます。 1 つのテーブルに単純な UPDATE を使用することもできたと思いますが、この例は、JOIN を行う際にどのように行うことができるかを示しています。
update invoiceset invoice.BillingCity='Wien'from invoiceinner join customeron invoice.CustomerId=customer.CustomerIdwhere customer.City='Vienne'
特定のフィルターをかけるために、JOIN句でCTE（Common Table Expression）を使用することもできます。
例えば、ロックとメタル(ジャンル1と3)に20ドル以上使ったオーストリアの顧客に対して、請求書の合計金額に特別な割引をする必要があるとします。
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
仮に20%の割引を適用したいとします。
; 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
これはOracleではどのように機能するのでしょうか。
同様のことを行うためには、いくつかのショートカットを行う必要があります。 サブクエリーとINフィルターを利用することができます。 例えば、最初のUPDATEをSQL Serverで使用したJOINで変換してみましょう。
まず、同じSELECTクエリを使って、OracleのInvoiceテーブルのデータをチェックしてみましょう。
select invoice.BillingCityfrom chinook.invoiceinner join chinook.customer on invoice.CustomerId=customer.CustomerIdwhere customer.City='Vienne';
上記のクエリをサブクエリとして使用してUPDATE文を変換しますが、更新を行うために主キーのInvoiceidを抽出しています。
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' );
コミットを忘れないでください。
commit;
これは非常に簡単でしたが、別の大きなテーブルに基づいてUPDATEを行い、別のテーブルの値を使用する必要があるとします。
SQL ServerのCTEで行ったのと同じUPDATEを行いたいとすると、次のコードでJOINの問題を解決することができます。
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;
お気づきかもしれませんが、SQL Serverで行った更新と同様に、CTEをサブクエリに変換し、Invoiceテーブルと結合しました。 しかし、今回は、更新したい主キーと合計値を含む SELECT 文です。 この結果を更新するテーブルとして入力しました。 回避策ではありますが、うまくいきましたね。 気をつけなければならないのは、結果が一意であることと、必要以上の行を更新しようとしないことです。
もっとエレガントな解決策として、MERGE文を利用する方法があります。 これは次のように記述します。
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;
ご覧のように、先ほどのソリューションよりも読みやすくなっています。 SQL Serverでは、MERGE文のパフォーマンスは必ずしも最高ではありません。
PostgreSQLのJoinによる更新文
PostgreSQLではどうでしょうか。 この場合、SQL Serverで機能する同じコンセプトが、PostgreSQLでも機能します。 結合を指定しないので、構文に少しだけ違いがあります。 しかし、WHERE句を使った古い結合構文を使用しています。
SQL Serverで使用したのと同じSQLコードをPostgreSQLのChinookデータベースでテストしてみましょう。
update "Invoice"set "BillingCity"='Wien'from "Customer"where "Invoice"."CustomerId"="Customer"."CustomerId"and "Customer"."City"='Vienne'
この場合、updateを行う最初のテーブルを指定する必要はありません。
CTEでコードをテストしてみましょう (PostgreSQLでは、大文字で作成されたすべての列名を引用符で囲む必要があり、そうしないと認識されないことに注意してください)。):
; 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"
概要
このヒントでは、SQL Server、Oracle、PostgreSQLでJOINを使用する際のUPDATE文の構文の違いを見てきました。
次のステップ
- Oracleでは、副問い合わせでWHERE EXISTS句を使用するなど、結合を使用して更新する他の方法があります。
- SQL Server では SSMS を、Oracle では SQL Developer を、PostgreSQL では PGAdmin を使用しました。
最終更新日: 2021-01-2012: 2021-01-04
著者についてh5 著者について
Andrea Gnemmi 氏は、約 20 年の経験を持つデータベースおよびデータウェアハウスの専門家です。 SQL Server 2000でデータベース管理のキャリアをスタートさせました。全てのTipsを見る
- More SQL Server DBA Tips…