PostgreSQL は MySQL と異なり、 UPDATE でテーブル結合をするときは JOIN が使えないことを最近知ったので記事に残しておきます。

結論

以下のような、2つのテーブルがあるとする
飼い主のテーブル（owners）はの名前とメガネの有無の情報を持っている。
ネコのテーブル（cats）は名前と首輪の有無と飼い主IDの情報を持っている。

owners テーブル

cats テーブル

id	cat_name	has_collar	owner_id
1	タマ	false	3
2	トロイ	true	3
3	ビヨーク	true	2
...	...	...	...
1000	ティグ	false	4

メガネをかけている人が飼っているのネコ全てに首輪を付けたいとき、以下のようにする。

UPDATE pets 
SET has_collar=true
FROM owners
WHERE pets.owner_id = owners.id
  AND has_glasses=true

やりがちなミス

「メガネをかけている人が飼っているのネコ」を調べるとき、

SELECT * 
FROM pets
JOIN owners
  ON pets.owner_id = owners.id
WHERE has_glasses=true

みたいな SELECT 文を書くから、下記のような UPDATE を書いてしまいがち。


UPDATE pets 
SET has_collar=true
FROM pets
JOIN owners
  ON pets.owner_id = owners.id
WHERE has_glasses=true

MySQL だと上記の方法でできるかもしれないけど、PostgreSQL だと

table name "pets" specified more than once

というエラーになる。