テーブル結合を伴う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=#例１ 相関サブクエリーを使ったUPDATE
おそらくこちらが一般的かとは思います。
update shop_ueno s set (price,discount) = ( select f.price,f.discount from fruit f where s.fid = f.fid );
例2 直接from句を指定するUPDATE
こちらは２つのテーブルを結合した結果に基づいて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);
