UPDATEのサブクエリで気を付けること（ORA-01779: キー保存されていない表にマップする列は変更できません）

OracleのUPDATEでサブクエリを使う場合、他のテーブルからデータを取得してきて、取得したデータを使って集計したり複雑なことをやろうとしたら、「ORA-01779: キー保存されていない表にマップする列は変更できません」というエラーが出て、やろうと思ったことができない可能性があります。

以下、どういった場合にこのエラーが発生するのかをメモしたものになります。

まずは、通常のUPDATEの例。

更新対象の商品テーブル（PRODUCT）に、以下のデータが登録されているとします。

◆商品テーブル（PRODUCTS）

ID	NAME	PRICE	NOTE
1	商品A	100
2	商品B	200
3	商品C	300
4	商品D		商品A、B、Cのセット商品

上記のデータの内、商品BのNAMEとPRICEを更新する場合、以下のようになります。

UPDATE PRODUCTS SET
  NAME = '商品B＋',
  PRICE = 250
WHERE 
  ID = 2;

続いて、上記で使用したテーブルとデータを使って、今度はSELECTで取得してきたデータをUPDATEしてみます。

商品DのPRICEに、商品A、B、Cの合計金額をセットする場合は、以下のようになります。

UPDATE PRODUCTS SET
  PRICE = ( SELECT SUM(PRICE) FROM PRODUCTS WHERE ID IN (1, 2, 3) )
WHERE ID = 4;

さらに、もう一つのパターンとして、新商品テーブル（PRODUCTS2）を別途作成して、２つのテーブルをJOINした上でUPDATEしてみます。

新しく作成するテーブルとデータは以下の通り。

◆新商品テーブル（PRODUCTS2）

ID	NEW_NAME	NEW_PRICE
1	商品A＋	150
2	商品B＋	250
3	商品C＋	350

上記のデータを利用して、商品テーブル（PRODUCTS）と新商品テーブル（PRODUCTS2）を連結して、商品BのNAMEとPRICEを更新します。

UPDATE (
  SELECT 
    PRODUCTS.ID, 
    PRODUCTS.NAME, 
    PRODUCTS.PRICE, 
    PRODUCTS2.NEW_NAME, 
    PRODUCTS2.NEW_PRICE
  FROM PRODUCTS
  INNER JOIN PRODUCTS2
    ON PRODUCTS2.ID = PRODUCTS.ID
  WHERE 
    PRODUCTS.ID = 2
) SET
  NAME = NEW_NAME,
  PRICE = NEW_PRICE;

ちなみに他のDBでは下記のような書き方ができましたが、OracleではNGです。

UPDATE PRODUCTS SET
  NAME = PRODUCT2.NEW_NAME
 ,PRICE = PRODUCT2.NEW_PRICE
FROM PRODUCTS
LEFT JOIN PRODUCTS2
  ON PRODUCTS2.ID = PRODUCTS.ID
WHERE 
  PRODUCTS.ID = 2;

実行すると、「ORA-00933: SQLコマンドが正しく終了されていません。」というエラーが発生するかと思います。

で、ここまでは、なんとかなるのでいいのですが、問題は次です。

まず、次のような更新SQLを作成します。

UPDATE (
  SELECT 
    PRODUCTS.ID, 
    PRODUCTS.NAME, 
    PRODUCTS.PRICE, 
    SET_PRODUCTS2.NEW_NAME, 
    SET_PRODUCTS2.NEW_PRICE
  FROM PRODUCTS
  INNER JOIN 
  (
    SELECT 
      4 AS ID, 
      '商品D＋' AS NEW_NAME, 
      SUM(NEW_PRICE) AS NEW_PRICE 
    FROM 
      PRODUCTS2
    WHERE 
      ID IN (1, 2, 3)
  ) SET_PRODUCTS2
    ON SET_PRODUCTS2.ID = PRODUCTS.ID
) SET
  NAME = NEW_NAME,
  PRICE = NEW_PRICE;

SQLの内容としては、新商品テーブル（PRODUCTS2）の商品A～Cの合計金額を、商品テーブル（PRODUCTS）の商品Dに対して反映しようとしています。

見た感じ、正常に実行されそうなSQLですが、Oracleの場合、これを実行すると、以下のようなエラーが発生します。

SQLエラー: ORA-01779: キー保存されていない表にマップする列は変更できません
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

上記のSQL以外にも、1対Nのテーブルを連結して処理しようとすると、同様なエラーが出てしまうので、サブクエリの中で複数のテーブルを連結して処理しようとすると、結局、更新できなかった、という出来事が何度かありました。

このエラーは、更新するレコードが一意にならない場合に発生するものらしいのですが、上記のSQLでは一意になっているのにエラーが出ています。

「BYPASS_UJVC」というヒントを付けてSQLを実行すれば動作するという情報もありましたが、それは古いバージョンのOracleのみで、11gからは使えなくなっているので、結論として、こういった場合、OracleではPL/SQLを使うしかないようです。

PL/SQLについてはまた別途、記事にしたいと思います。

改修作業や稼働後のデータコンバートなどでよく長めの更新用SQLを作成していたので、それが出来ないのは、ちょっと面倒です。

ただ、PL/SQLに慣れたら、それはそれで便利だったので、とりあえずは良しとします。