テーブル結合を行ったUPDATE
業務アプリではあまり見かけませんが、テストデータを作成したりテーブルデータの手動メンテナンスなどに活躍します。2種類の更新方法を記載します。
まずは今回のテストデータは以下の通りです
fruitテーブルのprice,discount項目値を、shop_uenoの項目へUPDATEします。
SQL> select s.FID,f.NAME,s.PRICE,s.DISCOUNT from shop_ueno s inner join fruit f on s.FID = f.FID
2 order by 1
3 ;
FID NAME PRICE DISCOUNT
---------- ---------- ---------- ----------
101 バナナ 250 0
102 リンゴ 190 0
104 ブドウ 290 0
SQL> select * from fruit order by 1;
FID NAME PRICE DISCOUNT
---------- ---------- ---------- ----------
101 バナナ 200 10
102 リンゴ 100 15
103 メロン 300 70
104 ブドウ 260 40
SQL>
例１ 相関サブクエリーを使ったUPDATE
おそらくこちらが一般的かとは思います。
update shop_ueno s set (price,discount) = ( select f.PRICE,f.DISCOUNT from fruit f where s.FID = f.FID );
SQL> update shop_ueno s set (price,discount) = (
2 select f.price,f.discount from fruit f
3 where s.fid = f.fid
4 );
3行が更新されました。
SQL> select s.FID,f.NAME,s.PRICE,s.DISCOUNT from shop_ueno s inner join fruit f on s.FID = f.FID
2 order by 1
3 ;
FID NAME PRICE DISCOUNT
---------- ---------- ---------- ----------
101 バナナ 200 10
102 リンゴ 100 15
104 ブドウ 260 40
-- 次のUPDATEに備えロールバック
--
SQL> rollback;
ロールバックが完了しました。例２ 高速にUPDATEをするなら
こちらの手法にどんな名前が付いているのか知りませんが案外知られていない方法だと思います。よく見ると理解しやすいですよね。但し前提条件があります。更新元のテーブル（以下ではfruit）のキー項目にはユニークキーが付与されている必要あります。それともう一つ、set で指定する項目を エイリアス指定してはいけません。もし結合するテーブルに同一名のカラムが存在する場合、以下SQLのようにas xxxとして重複のない別名を付ける必要があります。ただし、他のRDBにおいては利用できないものもあります。（少なくともPostgreSQLでは動作しません。Oracleだけかもしれません、、、）
update ( select s.fid,f.name ,s.price as s_price,s.discount as s_discount ,f.price as f_frice,f.discount as f_discount from shop_ueno s inner join fruit f on s.fid = f.fid ) set s_price = f_frice ,s_discount = f_discount ;
-- SETでエイリアス指定したために発生したエラー
--
SQL> update (
2 select s.fid,f.name
3 ,s.price ,s.discount
4 ,f.price ,f.discount
5 from shop_ueno s inner join fruit f on s.fid = f.fid
6 )
7 set
8 s.price = f.frice
9 ,s.discount = f.discount
10 ;
,s.discount = f.discount
*
行9でエラーが発生しました。:
ORA-00904: "F"."DISCOUNT": 無効な識別子です。
-- キー項目にユニークキーが付与されていない事で発生したエラー
--
SQL> update (
2 select s.fid,f.name
3 ,s.price as s_price,s.discount as s_discount
4 ,f.price as f_frice,f.discount as f_discount
5 from shop_ueno s inner join fruit f on s.fid = f.fid
6 )
7 set
8 s_price = f_frice
9 ,s_discount = f_discount
10 ;
s_price = f_frice
*
行8でエラーが発生しました。:
ORA-01779: キー保存されていない表にマップする列は変更できません
-- ユニークインデックスを作成し再実行
--（当然PKとしての作成でもOKです）
SQL> create unique index uni01_fruit on fruit(fid);
索引が作成されました。
SQL> update (
2 select s.fid,f.name
3 ,s.price as s_price,s.discount as s_discount
4 ,f.price as f_frice,f.discount as f_discount
5 from shop_ueno s inner join fruit f on s.fid = f.fid
6 )
7 set
8 s_price = f_frice
9 ,s_discount = f_discount
10 ;
3行が更新されました。
-- 無事UPDATEできました。
--
SQL> select s.FID,f.NAME,s.PRICE,s.DISCOUNT from shop_ueno s inner join fruit f on s.FID = f.FID
2 order by 1
3 ;
FID NAME PRICE DISCOUNT
---------- ---------- ---------- ----------
101 バナナ 200 10
102 リンゴ 100 15
104 ブドウ 260 40
SQL>
テストデータの作成
create table shop_ueno
(
fid number ,
price number,
discount number
);
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 number,
name varchar2(40),
price number,
discount number
);
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);
