Oracle 20-サブクエリ

DML文(UPDATE、INSERT、DELETE)
3)サブクエリを使用したUPDATE文
(フォーマットを使用)
UPDATEテーブル名
SET(カラム名[,カラム名,...])=(サブクエリ)--変更する列を単一の列として記述します.
[WHERE条件];
.SETセクションで複数のカラム名を使用する場合は、カラム名を「()」に組み合わせて説明します.
サブクエリのSELECTセクションのカラムリストは、数、順序、タイプと一致する必要があります.
を選択します.サブクエリを使用するSETセクションで単一のカラムを処理する場合は、複数のサブクエリが必要です.
--サブクエリの条件は、変更する値を決定するためにサブクエリから材料を取り出すことです.
--WHERE条件変更するデータを作成するための条件
--WHERE節担当行、更新する行数を抽出する条件
--更新するカラムは1つしかありません.2つのカラム値がサブクエリにある場合、
->サブクエリをサブクエリにマージする
使用例)従業員表で従業員の給与を所属部門の平均給与に更新してください.

(서브쿼리:부서별 평균급여)
    SELECT DEPARTMENT_ID,
           ROUND(AVG(SALARY)) AS ASAL
      FROM HR.EMP
     GROUP BY DEPARTMENT_ID; 

(메인쿼리:사원의 급여 변경)
    UPDATE HR.EMP A
       SET A.SALARY=(SELECT B.ASAL
                       FROM (SELECT DEPARTMENT_ID,
                                    ROUND(AVG(SALARY)) AS ASAL
                               FROM HR.EMP
                              GROUP BY DEPARTMENT_ID)B
                      WHERE A.DEPARTMENT_ID=B.DEPARTMENT_ID); 
                      --사원이 가지고있는 부서만 구별, 사원이 구별될 필요는 없음
     WHERE A.DEPARTMENT_ID=50;
     

使用例)2005年7月にユーザーの購入状況に応じて、会員のポイントを変更する
--購入活動の結果、CART、MEMBER、[PROD 140]

(서브쿼리: 2005년 7월 사용자별 추가 지급될 마일리지)--회원번호,지급될 마일리지
  SELECT D.SUMM
    FROM (SELECT A.CART_MEMBER AS AID,
                 SUM(A.CART_QTY*B.PROD_MILEAGE) AS SUMM
            FROM CART A, PROD B
           WHERE A.CART_NO LIKE '200507%'
             AND A.CART_PROD=B.PROD_ID
           GROUP BY A.CART_MEMBER)D 
  
(메인쿼리: 갱신)
  UPDATE MEMBER C
     SET MEM_MILEAGE =(SELECT C.MEM_MILEAGE+D.MSUM --가지고 있는 마일리지에 더해지는 마일리지
                          FROM (SELECT A.CART_MEMBER AS AID,
                                       SUM(A.CART_QTY*B.PROD_MILEAGE) AS MSUM
                                  FROM CART A, PROD B
                                 WHERE A.CART_NO LIKE '200507%'
                                   AND A.CART_PROD=B.PROD_ID
                              GROUP BY A.CART_MEMBER)D
                          WHERE C.MEM_ID=D.AID)    --변경할 값
   WHERE C.MEM_ID IN (SELECT DISTINCT CART_MEMBER
   --MEM_ID가 더 많기 때문에 '='가 아닌 IN연산자가 나와야한다.
                        FROM CART
                       WHERE CART_NO LIKE '200507%');
                       -- 2005년 7월에 판매실적이 있는 회원들만 골라서 변경해라 
   --바깥쿼리의 where절 : 변경할 사람을 고르는 조건                     
   
   SELECT MEM_ID,MEM_MILEAGE
     FROM MEMBER;

使用例)2005年5-7月の販売状況を確認し、在庫支払表を変更する

--서브쿼리
    SELECT CART_PROD,
           SUM(CART_QTY)
      FROM CART
     WHERE SUBSTR(CART_NO,1,6) BETWEEN '200505' AND '200507'  
     GROUP BY CART_PROD;

 --재고수불테이블 변경      

  UPDATE REMAIN A
   SET (A.REMAIN_O,A.REMAIN_J_99,A.REMAIN_DATE)=
       (SELECT A.REMAIN_O+B.CMT,A.REMAIN_J_99-B.CMT,TO_DATE('20050731')
          FROM (SELECT CART_PROD AS CID,
                       SUM(CART_QTY) AS CMT
                  FROM CART
                 WHERE SUBSTR(CART_NO,1,6) BETWEEN '200505' AND '200507'
                 GROUP BY CART_PROD) B
         WHERE A.PROD_ID=B.CID)
   WHERE A.PROD_ID IN (SELECT DISTINCT CART_PROD
                         FROM CART
                        WHERE SUBSTR(CART_NO,1,6) BETWEEN '200505' AND '200507'); 

使用例)2005年5-7月の購買状況を照会し、在庫支払表を変更してください.
--サブクエリ:5-7月の購買状況

UPDATE REMAIN A      
   SET (A.REMAIN_I,A.REMAIN_J_99,A.REMAIN_DATE)=
       (SELECT A.REMAIN_I+B.BMT,A.REMAIN_J_99+B.BMT,TO_DATE('20050731') 
          FROM (SELECT BUY_PROD AS BID,
                       SUM(BUY_QTY) AS BMT
                  FROM BUYPROD
                 WHERE BUY_DATE BETWEEN TO_DATE('20050501') AND TO_DATE('20050731') 
                 GROUP BY BUY_PROD)B
         WHERE A.PROD_ID=B.BID)
 WHERE A.PROD_ID IN (SELECT DISTINCT BUY_PROD
                       FROM BUYPROD
                      WHERE BUY_DATE BETWEEN TO_DATE('20050501') AND TO_DATE('20050731')); 

集合演算子を使用すると、サブクエリを減らすことができます.
4)サブクエリを使用したINSERT
(フォーマットを使用)
INSERT INTO表名[(列名[,列名,...])]
サブクエリ
.INSERTで使用されるサブクエリは「()」に記述されていません
▲サブクエリのINSERTを使用してVALES予約項目を省略
**テーブルのコピー
CREATE TABLE表名AS
「サブクエリ」--サブクエリの結果がテーブルに表示されます.
例)REMAINテーブルを作成し、REMAIN TEMPテーブルをコピーします.

 CREATE TABLE REMAIN_TEMP AS
   SELECT * FROM REMAIN; 
   --REMAIN테이블에 있는 모든 행과 열이 REMAIN_TEMP에 들어감

 SELECT * FROM REMAIN_TEMP; 

DELETE REMAIN_TEMP; --REMAIN의 모든 행을 삭제시킴
 (행을 담당하는 WHERE절이 생략되면 모든 행 삭제, TABLE 자체를 없애는 게 아님)

COMMIT;

使用例)「在庫引渡し」表で現在の高値マイナス値の資料を検索し、REMAIN TEMP表に保存します.

INSERT INTO REMAIN_TEMP
  SELECT * FROM REMAIN
   WHERE REMAIN_J_99<0;
   
 SELECT * FROM REMAIN_TEMP;