2020.12.02 Wednesday
ポスグレ、UPDATEにFROM句が使えるんだぜ、ワイルドだろう?
ポスグレ、UPDATEにFROM句が使えるんだぜ、ワイルドだろう?
本エントリはPostgreSQL Advent Calendar 2020の二日目(ピンチヒッター?)です。
# 実際は2020-12-04に書いてます。
通常RDBMSで、UPDATE文にFROMは使えないのですが、PostgreSQL(と、最近それに倣うことのおおいSQLiteの最近の版(2020-08-14 (3.33.0)より))とSQL Serverでは使えるというお話。
SQLiteのドキュメントとPostgreSQLのドキュメント基づいての説明です。
・なぜFROM句が必要なのか?
FROM句のアイデアは、データベース内の他のテーブルによってUPDATEステートメントを駆動できるようにするためです。更新対象のテーブルと他のテーブルを結合した結果で、更新対象のテーブルをUPDATEします。
・PostgreSQLでの実現方法
FROM句の構文を使用して、Acme Corporationを顧客とするセールスマンのセールスカウントを1増加させます。
UPDATE employees SET sales_count = sales_count + 1 FROM accounts
WHERE accounts.name = 'Acme Corporation'
AND employees.id = accounts.sales_person;
FROMにはemployeesテーブルは指定しないことに注意してください。
・SQL Serverとの違い
PostgreSQLのマニュアルに「他のデータベースシステムには、FROMオプション内で、対象テーブルが再度指定されることを前提として動作するものもあります。 これはPostgreSQLにおけるFROMの解釈方法とは異なります。 この拡張機能を使用するアプリケーションを移植する時は注意してください」の注記がありますが、これがSQL Serverです。
PostgreSQL風に書くと次のようになります。
UPDATE employees SET sales_count = sales_count + 1 FROM accounts, employee
WHERE accounts.name = 'Acme Corporation'
AND employees.id = accounts.sales_person;
ただ、これは旧来の結合方法なので、今風にこう書くべきでしょう。
UPDATE employees SET sales_count = sales_count + 1 FROM accounts INNER JOIN employee
ON employees.id = accounts.sales_person
WHERE accounts.name = 'Acme Corporation';
・MySQLの実現方法
MySQLではFROMの代わりに、UPDATE対象の表を書くところに直接JOINを記述できます。そのため、二つ(以上)のテーブルを更新することも可能です。先ほどのSQL Serverの例で書くと以下のクエリとなります。
UPDATE employees INNER JOIN accounts
ON employees.id = accounts.sales_person
SET employees.sales_count = employees.sales_count + 1
WHERE accounts.name = 'Acme Corporation';
ポスグレのUPDATE文における独特の芸風について紹介しました。
本エントリはPostgreSQL Advent Calendar 2020の二日目(ピンチヒッター?)です。
# 実際は2020-12-04に書いてます。
通常RDBMSで、UPDATE文にFROMは使えないのですが、PostgreSQL(と、最近それに倣うことのおおいSQLiteの最近の版(2020-08-14 (3.33.0)より))とSQL Serverでは使えるというお話。
SQLiteのドキュメントとPostgreSQLのドキュメント基づいての説明です。
・なぜFROM句が必要なのか?
FROM句のアイデアは、データベース内の他のテーブルによってUPDATEステートメントを駆動できるようにするためです。更新対象のテーブルと他のテーブルを結合した結果で、更新対象のテーブルをUPDATEします。
・PostgreSQLでの実現方法
FROM句の構文を使用して、Acme Corporationを顧客とするセールスマンのセールスカウントを1増加させます。
UPDATE employees SET sales_count = sales_count + 1 FROM accounts
WHERE accounts.name = 'Acme Corporation'
AND employees.id = accounts.sales_person;
FROMにはemployeesテーブルは指定しないことに注意してください。
・SQL Serverとの違い
PostgreSQLのマニュアルに「他のデータベースシステムには、FROMオプション内で、対象テーブルが再度指定されることを前提として動作するものもあります。 これはPostgreSQLにおけるFROMの解釈方法とは異なります。 この拡張機能を使用するアプリケーションを移植する時は注意してください」の注記がありますが、これがSQL Serverです。
PostgreSQL風に書くと次のようになります。
UPDATE employees SET sales_count = sales_count + 1 FROM accounts, employee
WHERE accounts.name = 'Acme Corporation'
AND employees.id = accounts.sales_person;
ただ、これは旧来の結合方法なので、今風にこう書くべきでしょう。
UPDATE employees SET sales_count = sales_count + 1 FROM accounts INNER JOIN employee
ON employees.id = accounts.sales_person
WHERE accounts.name = 'Acme Corporation';
・MySQLの実現方法
MySQLではFROMの代わりに、UPDATE対象の表を書くところに直接JOINを記述できます。そのため、二つ(以上)のテーブルを更新することも可能です。先ほどのSQL Serverの例で書くと以下のクエリとなります。
UPDATE employees INNER JOIN accounts
ON employees.id = accounts.sales_person
SET employees.sales_count = employees.sales_count + 1
WHERE accounts.name = 'Acme Corporation';
ポスグレのUPDATE文における独特の芸風について紹介しました。
JUGEMテーマ:コンピュータ