PostgreSQL

PostgreSQL 結合UPDATE

テーブル結合を伴うUPDATE

普段使う事が少ないのでつい忘れがちな結合UPDATEです。RDBMSによっても書き方が異なります。

まずは今回のテストデータは以下の通りです

fruitテーブルのprice,discount項目値を、shop_uenoの項目へUPDATEします。

myposdb=# select * from fruit order by 1
myposdb-# ;
 fid |  name  | price | discount
-----+--------+-------+----------
 101 | バナナ |   200 |       10
 102 | リンゴ |   100 |       15
 103 | メロン |   300 |       70
 104 | ブドウ |   260 |       40
(4 行)

myposdb=#
myposdb=# select * from shop_ueno order by 1;
 fid | price | discount
-----+-------+----------
 101 |   250 |        0
 102 |   190 |        0
 104 |   290 |        0
(3 行)

myposdb=#
例1 相関サブクエリーを使ったUPDATE

おそらくこちらが一般的かとは思います。

update shop_ueno s set (price,discount) = (
select f.price,f.discount from fruit f
where s.fid = f.fid
);
例2 直接from句を指定するUPDATE

こちらは2つのテーブルを結合した結果に基づいてUPDATEしています。参考までにですが、このSQLはOracleでは文法エラーになります。(Oracleのupdate 文では直接from句を利用できないためです)

update shop_ueno
set price = f.price, discount = f.discount
from fruit f
where shop_ueno.fid = f.fid;
-- 検証を始めるまえにトランザクションを開始します。
--
myposdb=# begin;
BEGIN

-- まずは相関サブクエリを使ったUPDATE
--
myposdb=*# update shop_ueno s set (price,discount) = (
myposdb(*# select f.price,f.discount from fruit f
myposdb(*# where s.fid = f.fid
myposdb(*# );
UPDATE 3

-- 更新結果を確認します
--
myposdb=*# select * from shop_ueno order by 1;
 fid | price | discount
-----+-------+----------
 101 |   200 |       10
 102 |   100 |       15
 104 |   260 |       40
(3 行)

myposdb=*#
-- 想定通り更新されている事確認できました。
-- 次のテストをするために更新データを元に戻します。
--
myposdb=*# rollback;
ROLLBACK

-- 念のため戻っている事確認します。
--
myposdb=# select * from shop_ueno order by 1;
 fid | price | discount
-----+-------+----------
 101 |   250 |        0
 102 |   190 |        0
 104 |   290 |        0
(3 行)

myposdb=#
-- 次に直接from句を指定するupdateで更新してみます。
--
myposdb=# update shop_ueno
myposdb-# set price = f.price, discount = f.discount
myposdb-# from fruit f
myposdb-# where shop_ueno.fid = f.fid;
UPDATE 3

-- 更新結果を確認してみます。
--
myposdb=*# select * from shop_ueno order by 1;
 fid | price | discount
-----+-------+----------
 101 |   200 |       10
 102 |   100 |       15
 104 |   260 |       40
(3 行)

myposdb=*#
-- こちらも想定通り更新されている事確認できました。
--
myposdb=*# rollback;
ROLLBACK
myposdb=#

今回の検証データは以下のように作成しました

テストデータの作成
create table shop_ueno
(
fid integer ,
price integer,
discount integer
);

insert into shop_ueno(fid,price,discount) values(101,250,0);
insert into shop_ueno(fid,price,discount) values(102,190,0);
insert into shop_ueno(fid,price,discount) values(104,290,0);

create table fruit
(
fid integer,
name char varying(40),
price integer,
discount integer
);

create unique index uni01_fruit on fruit(fid);
insert into fruit(fid,name,price,discount) values(101,'バナナ',200,10);
insert into fruit(fid,name,price,discount) values(102,'リンゴ',100,15);
insert into fruit(fid,name,price,discount) values(103,'メロン',300,70);
insert into fruit(fid,name,price,discount) values(104,'ブドウ',260,40);

Oracle版はこちら

スポンサーリンク
コピペで使う