프로그래밍/DB

[oracle] select 문을 이용한 update 2

galad 2010. 8. 16. 17:49

UPDATE /*+ bypass_ujvc */

(

           SELECT A.CATEGORY_ID

           FROM DEPLOY_PRODUCT_CATEGORY A, PRODUCT_INFO B

           WHERE A.CATEGORY_ID LIKE '%DP070%'

                     AND A.PRODUCT_ID = B.PRODUCT_ID

                     AND B.STATUS LIKE '6%'

)

SET CATEGORY_ID = 'XXXXXXXX';

/*+ bypass_ujvc */ 이 부분 필수임.

다음과 같이 SELECT에서 2개의 컬럼을 가져다가 넣는 것도 가능

update /*+ bypass_ujvc */

(
   select e.PRODUCT_ID, e.PROD_DESC o_PROD_DESC,f.PROD_DESC N_PROD_DESC
   from (
     select b.CHANNEL_ID,c.PRODUCT_ID,max(d.PROD_DESC) PROD_DESC
     from PRODUCT_INFO a, PRODUCT_CATEGORY b, PRODUCT_INFO c, TBL_DP_PROD d
     where a.CONTENT_TYPE='09'
       and a.PRODUCT_ID = b.CHANNEL_ID
       and b.PRODUCT_ID = c.PRODUCT_ID
       and b.PRODUCT_ID <> b.CHANNEL_ID
       and b.CHANNEL_ID = d.PROD_ID
    group by b.CHANNEL_ID,c.PRODUCT_ID
  ) e, TBL_DP_PROD f
  where e.PRODUCT_ID = f.PROD_ID

    and f.PROD_DESC is null
)

set N_PROD_DESC = o_PROD_DESC


저작자표시 비영리 동일조건 (새창열림)