SQL：PostgreSQLの更新SQLで利用できる機能についてまとめ
PostgreSQLの更新SQLで利用できる機能についてまとめました。PostgreSQLはバージョン12を利用しました。
PostgreSQLではデータの更新SQLに以下の独自の拡張があります。
- FROM句を利用することができる
- RETURNING句を利用することができる
- WITH句を利用することができる
今回はこれらの独自拡張についてそれぞれの動作を確認していこうと思います。
利用するデータの準備
まず利用するデータを準備します。今回はサンプルとして以下のようなカテゴリテーブルと商品マスタテーブルを利用します。
DROP TABLE IF EXISTS 取扱商品;
DROP TABLE IF EXISTS 商品マスタ;
DROP TABLE IF EXISTS カテゴリ;
CREATE TABLE カテゴリ (
カテゴリID SERIAL PRIMARY KEY,
カテゴリ名 TEXT NOT NULL
);
CREATE TABLE 商品マスタ (
商品ID SERIAL PRIMARY KEY,
商品名 TEXT NOT NULL,
カテゴリID INTEGER NOT NULL,
標準価格 INTEGER NOT NULL,
FOREIGN KEY (カテゴリID) REFERENCES カテゴリ(カテゴリID)
ON DELETE CASCADE
);
CREATE INDEX 商品マスタ_カテゴリID ON 商品マスタ(カテゴリID);
CREATE TABLE 取扱商品 (
商品ID INTEGER,
店舗コード TEXT,
取扱価格 INTEGER,
取扱開始日 DATE,
取扱終了日 DATE,
PRIMARY KEY (商品ID, 店舗コード),
FOREIGN KEY (商品ID) REFERENCES 商品マスタ(商品ID)
ON DELETE CASCADE
);取扱商品テーブルは店舗ごとに取り扱う商品を表現しています。
登録するテスト用のデータは以下です。
DELETE FROM 取扱商品;
DELETE FROM 商品マスタ;
DELETE FROM カテゴリ;
SELECT SETVAL('カテゴリ_カテゴリID_SEQ', 1, false);
SELECT SETVAL('商品マスタ_商品ID_SEQ', 1, false);
INSERT INTO カテゴリ
(カテゴリ名)
VALUES
('日用品'),
('食料品'),
('ペット用品');
INSERT INTO 商品マスタ
(商品名, カテゴリID, 標準価格)
VALUES
('ティッシュペーパー', 1, 220),
('トイレットペーパー', 1, 200),
('チーズハンバーグ', 2, 300),
('ドッグフード', 3, 150),
('キャットフード', 3, 120);
INSERT INTO 取扱商品
(商品ID, 店舗コード, 取扱価格, 取扱開始日, 取扱終了日)
VALUES
(1, '0001', 220, '2022-01-01', '2999-12-31'),
(1, '0002', 220, '2022-01-01', '2999-12-31'),
(1, '0003', 220, '2022-01-01', '2999-12-31'),
(2, '0001', 200, '2022-01-01', '2999-12-31'),
(2, '0002', 200, '2022-01-01', '2999-12-31'),
(3, '0001', 300, '2022-01-01', '2999-12-31'),
(3, '0003', 300, '2022-01-01', '2999-12-31'),
(4, '0001', 150, '2022-01-01', '2999-12-31'),
(4, '0002', 150, '2022-01-01', '2999-12-31'),
(5, '0001', 120, '2022-01-01', '2999-12-31'),
(5, '0003', 120, '2022-01-01', '2999-12-31');実施する更新処理について
今回利用するデータのカテゴリテーブルと商品マスタテーブルは主キーを自動採番としました。そのため以下の前提で話を進めたいと思います。
- いくつか環境がある（開発用、試験用、商用など）
- 基本的にデータの内容は同じだが自動採番の項目については同じではないかもしれない
カテゴリが「ペット用品」の商品の取扱いを終了することを考えます。
具体的にどうするかというと、カテゴリが「ペット用品」の取扱商品の「取扱終了日」に取扱いを終了する日付を設定します。
そうすることで、その商品の取扱いの終了を表現することにします。
また、取扱いを終了する商品は在庫を処分してしまいたいので商品マスタに設定されている標準価格価格の半額で販売することにします。
やりたいことを簡単に整理すると以下になります。
- カテゴリが「ペット用品」の取扱商品の「取扱終了日」を更新する
- カテゴリが「ペット用品」の取扱商品の「取扱価格」をその商品の「標準価格」の半額に更新する
まず思いつくのは以下のような更新SQLではないでしょうか。
UPDATE 取扱商品
SET 取扱終了日 = '2022-07-31'
, 取扱価格 = (
SELECT TRUNC(商品マスタ.標準価格 / 2)
FROM 商品マスタ
WHERE 商品マスタ.商品ID = 取扱商品.商品ID
)
WHERE EXISTS (
SELECT *
FROM 商品マスタ
WHERE 商品マスタ.商品ID = 取扱商品.商品ID
AND EXISTS (
SELECT *
FROM カテゴリ
WHERE カテゴリ.カテゴリID = 商品マスタ.カテゴリID
AND カテゴリ.カテゴリ名 = 'ペット用品'
)
);更新SQLのPostgreSQL独自の拡張を利用する
それではPostgreSQL独自の拡張を上記の更新処理にそれぞれあてはめて確認していきたいと思います。
FROM句を利用する
PostgreSQLでは更新SQLにFROM句を追加して別のテーブルのレコードを利用してデータの更新を行うことができます。
FROM句を利用して商品マスタテーブルからカテゴリが「ペット用品」の商品を抽出、その商品の商品IDを用いて取扱商品を更新するように変更したSQLは以下です。
UPDATE 取扱商品
SET 取扱終了日 = '2022-07-31'
, 取扱価格 = TRUNC(商品マスタ.標準価格 / 2)
FROM
カテゴリ
INNER JOIN 商品マスタ
ON 商品マスタ.カテゴリID = カテゴリ.カテゴリID
WHERE 取扱商品.商品ID = 商品マスタ.商品ID
AND カテゴリ.カテゴリ名 = 'ペット用品';先ほどのSQLよりもかなりすっきりしたと思います。
RETURNING句を利用する
PostgreSQLではRETURNING句を利用することで更新SQLの場合でも結果を返却することができます。
UPDATE 取扱商品
SET 取扱終了日 = '2022-07-31'
, 取扱価格 = TRUNC(商品マスタ.標準価格 / 2)
FROM
カテゴリ
INNER JOIN 商品マスタ
ON 商品マスタ.カテゴリID = カテゴリ.カテゴリID
WHERE 取扱商品.商品ID = 商品マスタ.商品ID
AND カテゴリ.カテゴリ名 = 'ペット用品'
RETURNING 取扱商品.*;上記の例では取扱商品を更新してRETURNING句に「取扱商品.*」を指定しています。実行すると、以下ように更新した取扱商品の値が返却されます。
商品id | 店舗コード | 取扱価格 | 取扱開始日 | 取扱終了日
--------+------------+----------+------------+------------
4 | 0001 | 75 | 2022-01-01 | 2022-07-31
4 | 0002 | 75 | 2022-01-01 | 2022-07-31
5 | 0001 | 60 | 2022-01-01 | 2022-07-31
5 | 0003 | 60 | 2022-01-01 | 2022-07-31
(4 rows)
UPDATE 4FROM句に指定したテーブルの列をRETURNING句に指定することもできます。
UPDATE 取扱商品
SET 取扱終了日 = '2022-07-31'
, 取扱価格 = TRUNC(商品マスタ.標準価格 / 2)
FROM
カテゴリ
INNER JOIN 商品マスタ
ON 商品マスタ.カテゴリID = カテゴリ.カテゴリID
WHERE 取扱商品.商品ID = 商品マスタ.商品ID
AND カテゴリ.カテゴリ名 = 'ペット用品'
RETURNING
カテゴリ.カテゴリ名,
商品マスタ.商品名,
取扱商品.店舗コード,
商品マスタ.標準価格,
取扱商品.取扱価格;結果は以下のようになります。
カテゴリ名 | 商品名 | 店舗コード | 標準価格 | 取扱価格
------------+----------------+------------+----------+----------
ペット用品 | ドッグフード | 0001 | 150 | 75
ペット用品 | ドッグフード | 0002 | 150 | 75
ペット用品 | キャットフード | 0001 | 120 | 60
ペット用品 | キャットフード | 0003 | 120 | 60
(4 rows)
UPDATE 4さらに以下のようにWITH句を利用することでRETURNING句で戻される結果をソートすることもできます。
WITH 更新結果 AS (
UPDATE 取扱商品
SET 取扱終了日 = '2022-07-31'
, 取扱価格 = TRUNC(商品マスタ.標準価格 / 2)
FROM
カテゴリ
INNER JOIN 商品マスタ
ON 商品マスタ.カテゴリID = カテゴリ.カテゴリID
WHERE 取扱商品.商品ID = 商品マスタ.商品ID
AND カテゴリ.カテゴリ名 = 'ペット用品'
RETURNING 取扱商品.*
)
SELECT * FROM 更新結果 ORDER BY 店舗コード, 商品ID;結果は以下のように「ORDER BY」で指定した列でソートされます。
商品id | 店舗コード | 取扱価格 | 取扱開始日 | 取扱終了日
--------+------------+----------+------------+------------
4 | 0001 | 75 | 2022-01-01 | 2022-07-31
5 | 0001 | 60 | 2022-01-01 | 2022-07-31
4 | 0002 | 75 | 2022-01-01 | 2022-07-31
5 | 0003 | 60 | 2022-01-01 | 2022-07-31
(4 rows)データを更新して、結果の確認を行いたいような場面や、更新したレコードを利用して追加で処理を行う必要がある場面でRETURNING句が役に立ちそうです。
またRETURNING句は登録SQLや削除SQLでも利用することができます。
登録SQLでの利用の例
INSERT INTO カテゴリ
(カテゴリ名)
VALUES
('事務用品')
RETURNING *;結果は以下のように登録時に自動で採番される値も取得することができます。
カテゴリid | カテゴリ名
------------+------------
4 | 事務用品
(1 row)
INSERT 0 1削除SQLでの利用の例
DELETE FROM カテゴリ
WHERE カテゴリ名 = '事務用品'
RETURNING *;結果は以下のように削除されたデータが返却されます。
カテゴリid | カテゴリ名
------------+------------
4 | 事務用品
(1 row)
DELETE 1WITH句を利用する
PostgreSQLでは更新SQLでもWITH句を利用することができます。
WITH 更新対象商品 AS (
SELECT
商品マスタ.商品ID
, TRUNC(商品マスタ.標準価格 / 2) AS 取扱価格
FROM
商品マスタ
INNER JOIN カテゴリ
ON カテゴリ.カテゴリID = 商品マスタ.カテゴリID
WHERE カテゴリ.カテゴリ名 = 'ペット用品'
)
UPDATE 取扱商品
SET 取扱終了日 = '2022-07-31'
, 取扱価格 = 更新対象商品.取扱価格
FROM 更新対象商品
WHERE 取扱商品.商品ID = 更新対象商品.商品ID
RETURNING 取扱商品.*;結果は以下のようになります。
商品id | 店舗コード | 取扱価格 | 取扱開始日 | 取扱終了日
--------+------------+----------+------------+------------
4 | 0001 | 75 | 2022-01-01 | 2022-07-31
4 | 0002 | 75 | 2022-01-01 | 2022-07-31
5 | 0001 | 60 | 2022-01-01 | 2022-07-31
5 | 0003 | 60 | 2022-01-01 | 2022-07-31
(4 rows)
UPDATE 4上記の例ではWITH句を利用しない場合と比べ、そこまで見通しが良くなっているようには見えませんが、複雑なテーブル結合を行う場合にはWITH句を利用することで見通しがかなり良くなるかと思います。
まとめ
PostgreSQLでは更新SQLに独自の拡張があり、データを更新する作業などで便利に利用することができそうです。 詳細は以下のPostgreSQLのドキュメントのページに記載されていますので参照してみてはいかがでしょうか。
UPDATE — テーブルの行を更新する