PostgreSQLでUPDATE。

はじめに

業務で使用するのはRedshiftが中心ですが、ローカル環境でちょっと試したい場合は元になっているPostgreSQLを使っています。
基本INSERTばかりであまりUPDATEを使ってませんでしたが、調べたので書いておきます。

環境

Mac OSX 10.10.5 Yosemite
PostgreSQL 9.5.1

使用するテーブル

/*スキーマ作成*/
CREATE SCHEMA blog;

/*テーブルＡ*/
CREATE TABLE blog.table_a (
  id SERIAL
  ,val INTEGER
  ,PRIMARY KEY(id)
);
INSERT INTO blog.table_a VALUES
(1, 101), (2, 102), (3, 103);

/*テーブルＢ*/
CREATE TABLE blog.table_b (
  id SERIAL
  ,val INTEGER
  ,PRIMARY KEY(id)
);
INSERT INTO blog.table_b VALUES
(0, 100), (2, 112), (3, 113);

/*テーブルC*/
CREATE TABLE blog.table_c (
  id SERIAL
  ,val INTEGER
  ,PRIMARY KEY(id)
);
INSERT INTO blog.table_c VALUES
(2, 212), (3, 313);

postgres=# select * from blog.table_a;    
 id | val 
----+-----
  1 | 101
  2 | 102
  3 | 103
(3 rows)

postgres=# select * from blog.table_b;
 id | val 
----+-----
  0 | 100
  2 | 112
  3 | 113
(3 rows)

postgres=# select * from blog.table_c;
 id | val 
----+-----
  2 | 212
  3 | 313
(2 rows)

１、通常のWHEREを使用したUPDATE

テーブルＡのidが１のレコードのidとvalを更新。

SQL

UPDATE blog.table_a
SET id = 0, val = 999
WHERE id = 1;

複数カラムを更新できます。

実行結果

postgres=# select * from blog.table_a order by id;  
 id | val 
----+-----
  0 | 999
  2 | 102
  3 | 103
(3 rows)

id「1」が「0」に更新され、valも「999」に更新されました。

２、FROM、WHEREを使用したUPDATE

テーブルＢの中で、テーブルＡのidが一致するvalを更新する。

SQL

UPDATE blog.table_a
SET val = B.val
FROM blog.table_b AS B
WHERE table_a.id = B.id;

UPDATE句のテーブルには別名を設定できないので、WHERE句で「table_a.id」などと書かないといけません。

実行結果

postgres=# select * from blog.table_a;  
 id | val 
----+-----
  0 | 100
  2 | 112
  3 | 113
(3 rows)

テーブルＢのidは「0」「2」「3」を持っているので全てのvalが更新されました。

３、FROM、JOINを使用したUPDATE

テーブルＢとテーブルＣをidで結合した結果の中で、テーブルＡのidが一致するvalを更新

SQL

UPDATE blog.table_a
SET val = C.val
FROM blog.table_b AS B
       LEFT OUTER JOIN blog.table_c AS C ON C.id = B.id
WHERE blog.table_a.id = C.id;

テーブルＡのidが同じ場合のみ、テーブルＢとテーブルCを結合した結果の値で更新。

実行結果

postgres=# select * from blog.table_a;  
 id | val 
----+-----
  0 | 100
  2 | 212
  3 | 313
(3 rows)

テーブルＣのidは「2」「3」をもっているので、一致するレコードのvalのみ更新されました。

さいごに

FROMを使用した場合に条件に一致した項目が複数存在すると、思わぬ結果になりますので注意が必要です。