【PostgresSQL】他のテーブル内容を元にUPDATEを行うサンプル

下記のようなデータ構成/テーブル構成だったとする

parentsテーブル
id	name
1	AAA
2	BBB

childrenテーブル
id	parent_id	name
1	1	zzz
2	2	yyy
3	1	xxx
4	3	www

children.parents_id と parents.idでリレーションを持っているものとし、このとき、parents.nameが’AAA’の、children.nameの値を更新する、という処理のサンプルのメモとなります。

検証用テーブル/データの準備
1. テーブルの準備
2. データの準備
UPDATE FROM 構文で更新してみる
サブクエリを使って更新してみる

検証用テーブル/データの準備

テーブルの準備

-- Table: public.parents
-- DROP TABLE IF EXISTS public.parents;
CREATE TABLE IF NOT EXISTS public.parents
(
    id bigint NOT NULL,
    name text COLLATE pg_catalog."default",
    CONSTRAINT pk PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.parents
    OWNER to postgres;


-- Table: public.children
-- DROP TABLE IF EXISTS public.children;
CREATE TABLE IF NOT EXISTS public.children
(
    id bigint NOT NULL,
    parent_id bigint,
    name text COLLATE pg_catalog."default",
    CONSTRAINT children_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.children
    OWNER to postgres;

データの準備

TRUNCATE public.children, public.parents;

INSERT INTO public.parents
VALUES
(1, 'AAA')
,(2,'BBB');

INSERT INTO public.children
VALUES
(1,1, 'zzz')
,(2,2, 'yyy')
,(3,1, 'xxx')
,(4,3, 'www');

UPDATE FROM 構文で更新してみる

※少し特殊な書き方をしています。withとかはない方がシンプルかもしれないです。

WITH cte	
AS	
(	
	SELECT 
	CAST ('AAA' as text) AS parent_name
	,CAST ('updated!' as text) AS update_text
)	
UPDATE children	
SET name = cte.update_text	
FROM parents, cte	
WHERE children.parent_id = parents.id	
AND parents.name = cte.parent_name

上記の実行後のテーブル内容↓

parentsテーブル
id	name	
1	AAA
2	BBB

childrenテーブル
id	parent_id	name
1	1	updated!
2	2	yyy
3	1	updated!
4	3	www

childrenテーブルの１行目、３行目を「updated!」に更新できています。

↓挙動の詳細は下記に記載されているのですが、FROMを使って結合した結果、結合対象のレコード１行につき複数行が結合されてしまう場合は、どう更新されるのか簡単には判断できないようで、結合したいレコード１行につき、１行または０行が紐づくように注意する必要がありそうです。

UPDATE

サブクエリを使って更新してみる

サブクエリを使った場合は上記のような感じになるかと思います。

WITH cte
AS
(
	SELECT
	CAST ('AAA' as text) AS parent_name
	,CAST ('updated!' as text) AS update_text
)
UPDATE children
SET name = cte.update_text
FROM parents, cte
WHERE parent_id =
	(
		SELECT
		id
		FROM parents
		WHERE name = cte.parent_name
	);

↑の実行結果が下記ですが、同様に更新できています。

"id"	"name"
1	"AAA"
2	"BBB"

"id"	"parent_id"	"name"
1	1	"updated!"
2	2	"yyy"
3	1	"updated!"
4	3	"www"