단순 다음에 기억이 안날까봐 여기에 적어 놓음.. ;;

형식 - UPDATE {TABLE} SET {COLUMN} FROM {SUB_QUERY} WHERE {CONDITION}

아래는 사용한 예..

UPDATE CUSTOMER_AUTO
SET   CUSTOMER_AUTO.WARRANTY_EXPIRED_DATE = dateadd(day, -1, dateadd(month, RTL.PERIOD_1, CUSTOMER_AUTO.REGIST_DATE))
    , CUSTOMER_AUTO.SERVICE_EXPIRED_DATE = dateadd(day, -1, dateadd(month, RTL.PERIOD_2, CUSTOMER_AUTO.REGIST_DATE))
FROM (
    SELECT A.COMM_NO
        , CASE WHEN A.DELIVERY_PRICE_SEQ = 'CS0001' THEN C.WARRANTY_PERIOD_GENERAL
              WHEN A.DELIVERY_PRICE_SEQ = 'CS0002' THEN C.WARRANTY_PERIOD_TAX_FREE
              WHEN A.DELIVERY_PRICE_SEQ = 'CS0003' THEN C.WARRANTY_PERIOD_DIPLOMAT
              WHEN A.DELIVERY_PRICE_SEQ = 'CS0006' THEN C.WARRANTY_PERIOD_RENT
              WHEN A.DELIVERY_PRICE_SEQ = 'CS0005' OR A.DELIVERY_PRICE_SEQ = 'CS0004' THEN 36
              ELSE 0 END
        AS PERIOD_1
        , CASE WHEN A.DELIVERY_PRICE_SEQ = 'CS0001' THEN C.WITHOUT_COST_PERIOD_GENERAL
              WHEN A.DELIVERY_PRICE_SEQ = 'CS0002' THEN C.WITHOUT_COST_PERIOD_TAX_FREE
              WHEN A.DELIVERY_PRICE_SEQ = 'CS0003' THEN C.WITHOUT_COST_PERIOD_DIPLOMAT
              WHEN A.DELIVERY_PRICE_SEQ = 'CS0006' THEN C.WITHOUT_COST_PERIOD_RENT
              WHEN A.DELIVERY_PRICE_SEQ = 'CS0005' OR A.DELIVERY_PRICE_SEQ = 'CS0004' THEN 36
              ELSE 0 END
        AS PERIOD_2
    FROM SALE_CONFER A
    INNER JOIN CUSTOMER_AUTO D ON A.COMM_NO = D.COMM_NO AND D.REGIST_DATE IS NOT NULL AND (D.WARRANTY_EXPIRED_DATE IS NULL OR D.SERVICE_EXPIRED_DATE IS NULL)
    INNER JOIN STOCK B ON A.COMM_NO = B.COMM_NO
    INNER JOIN AUTO_YEARS C ON A.AUTO_MODEL = C.AUTO_MODEL AND B.YEAR_SEQ = C.YEAR_SEQ
    WHERE A.DELIVERY_PRICE_SEQ IS NOT NULL AND A.COMM_NO IS NOT NULL AND A.SALE_FIXED_DATE >= {ts '2010-05-17 00:00:00'}
) RTL
WHERE CUSTOMER_AUTO.COMM_NO = RTL.COMM_NO AND REGIST_DATE IS NOT NULL AND (WARRANTY_EXPIRED_DATE IS NULL OR SERVICE_EXPIRED_DATE IS NULL) AND CUSTOMER_AUTO.COMM_NO IS NOT NULL


'DB > MSSQL' 카테고리의 다른 글

SELECT 후 UPDATE  (0) 2010.06.08
MS-SQL JDBC Library  (0) 2009.12.09

+ Recent posts