仕事でUPDATE文を書いた際にミスをしたので忘れないように備忘録。
初歩的な内容だと思います。
前提
以下のようなテーブル定義とデータがあったとする
CREATE TABLE companies(
id bigint primary key,
name varchar(50) NOT NULL,
tel varchar(50)
);
CREATE TABLE employees(
id bigint primary key,
company_id bigint,
name varchar(50) NOT NULL,
age integer,
department varchar(50),
FOREIGN KEY (company_id) references companies(id)
);
INSERT INTO public.companies(id, name, tel)
VALUES
(1, '株式会社ほげ', '00-0000-0000'),
(2, '株式会社ふが', '00-0000-1111'),
(3, '株式会社ぴよ', '00-0000-2222');
INSERT INTO public.employees(id, company_id, name, age, department)
VALUES
(1, 1, 'テスト太郎', 25, '人事部'),
(2, 1, 'テスト次郎', 20, '営業部'),
(3, 2, 'テスト三郎', 30, '人事部'),
(4, null, 'テスト五郎', 40, '営業部');
やりたいこと
Companiesテーブルの name = 株式会社ほげ に一致するものを対象に、
Emoloyeesテーブルの department = 人事部 に一致するものを 人事採用部 に変える
employeesのid:1のレコードだけが書き換わる想定。
※他テーブルとJOINしたい例なので、例題がおかしいことは気にしない。
だめな例
UPDATE employees
SET department = '人事採用部'
FROM employees e
JOIN companies c ON c.id = e.company_id
WHERE c.name = '株式会社ほげ' AND e.department = '人事部'
結果
| id | company_id | name | age | department |
|---|---|---|---|---|
| 1 | 1 | テスト太郎 | 25 | 人事採用部 |
| 2 | 1 | テスト次郎 | 20 | 人事採用部 |
| 3 | 2 | テスト三郎 | 30 | 人事採用部 |
| 4 | テスト五郎 | 40 | 人事採用部 |
すべてのレコードが書き換わってしまう。
なぜか？
postgresのドキュメントより引用
FROM句が存在する場合、基本的に、対象テーブルとfrom_itemリストで指定されたテーブルが結合され、この結合の出力行が対象テーブルの更新操作の結果となります。 FROM句を使用する場合、更新対象テーブルの1行に対して、結合 結果が複数行にならないように注意してください。 言い換えると、対象テーブルの個々の行は、他テーブルの複数の行と結合すべきではありません。 結合結果が複数行になった場合、対象行の更新には結合結果のいずれか1行のみが使用.されますが、どの行が使用されるかは簡単には予測できません。
UPDATE で指定した employees と FROM で指定した employees が結合してしまうのが原因。
UPDATE の employees には何も条件が付与されていないため、全レコード取得して更新を行ってしまう。
どう書くべきか
1. UPDATEとFROMのテーブルに条件を追加する
UPDATE employees e1
SET department = '人事採用部'
FROM employees e2
JOIN companies c ON c.id = e2.company_id
WHERE e1.id = e2.id AND c.name = '株式会社ほげ' AND e2.department = '人事部'
2. FROMに結合したいテーブルを定義する
JOINを使わない方法。
こちらのほうがスマートだと思われる。
UPDATE employees e
SET department = '人事採用部'
FROM companies c
WHERE e.company_id = c.id AND c.name = '株式会社ほげ' AND e.department = '人事部'