서브 쿼리 업데이트 관련 질문입니다.

WITH QQ AS
(SELECT 1234 RS_NO, 1000 IN_PAY, -100 OUT_PAY,’030105’ UP_DATE ,1234 USR_NO,0 STATE FROM DUAL UNION ALL
SELECT 1235 RS_NO, 2000 IN_PAY, 0 OUT_PAY,’030105’ UP_DATE ,1234 USR_NO,0 STATE FROM DUAL UNION ALL
SELECT 1236 RS_NO, 500 IN_PAY, 0 OUT_PAY,’030105’ UP_DATE ,1234 USR_NO,0 STATE FROM DUAL UNION ALL
SELECT 1237 RS_NO, 700 IN_PAY, -200 OUT_PAY,’030106’ UP_DATE ,1234 USR_NO,0 STATE FROM DUAL UNION ALL
SELECT 1238 RS_NO, 100 IN_PAY, 0 OUT_PAY,’030105’ UP_DATE ,1234 USR_NO,0 STATE FROM DUAL UNION ALL
SELECT 1239 RS_NO, 600 IN_PAY, 0 OUT_PAY,’030105’ UP_DATE ,1234 USR_NO,0 STATE FROM DUAL UNION ALL
SELECT 1240 RS_NO, 100 IN_PAY, 0 OUT_PAY,’030105’ UP_DATE ,1234 USR_NO,0 STATE FROM DUAL)
SELECT * FROM QQ

여기에서 -4500원이라는 입력을 넣었을 때... 아래와 같은 결과를 얻고 싶습니다.

이렇게 만들어진 결과물로 QQ라는 테이블을 업데이트를 시키고 싶습니다.

MODEL 절로 어떻게 해 볼까 하고.. 고민하다가 그만.ㅠ..ㅠ 능력이 부족해 쿼리로 해 버렸습니다.

조금더 낳은 방안이 있으시다면 조언 부탁드립니다.

쿼리만 풀었을 경우

SELECT RS_OUT_PAY,RS_UPDATE,DECODE(USR_RS_IN_PAY+RS_OUT_PAY,0,1,0) RS_STATE
FROM (SELECT USR_RS_NO,CASE WHEN SR<=0 THEN USR_RS_IN_PAY*-1 ELSE LEAST((USR_RS_IN_PAY-SR)*-1,0) END RS_OUT_PAY, TOD_STAMP(SYSDATE) RS_UPDATE,USR_RS_IN_PAY
FROM (SELECT USR_RS_NO,USR_RS_IN_PAY,USR_RS_OUT_PAY,SUM(USR_RS_IN_PAY+USR_RS_OUT_PAY) OVER(ORDER BY USR_RS_NO) + -4500 SR
FROM QQ
WHERE USR_NO=1234 AND USR_RS_STATE=0)
WHERE USR_RS_OUT_PAY<>CASE WHEN SR<=0 THEN USR_RS_IN_PAY*-1 ELSE LEAST((USR_RS_IN_PAY-SR)*-1,0) END
)

상위 쿼리를  UPDATE 에 적용 할려니.. WHERE 절에도

한번더 계산을 없앤 서브쿼리를 더 날려야 되네요... 좋은 방안이 없을 까요?

제가 UPDATE를 하는 방법입니다. 돌아가긴 하지만 문제는 부하가 걱정입니다.

UPDATE QQ A
        SET (A.USR_RS_OUT_PAY,A.USR_RS_UPDATE,A.USR_RS_STATE)=(SELECT RS_OUT_PAY,RS_UPDATE,DECODE(USR_RS_IN_PAY+RS_OUT_PAY,0,1,0) RS_STATE
                                                                  FROM (SELECT USR_RS_NO,CASE WHEN SR<=0 THEN USR_RS_IN_PAY*-1 ELSE LEAST((USR_RS_IN_PAY-SR)*-1,0) END RS_OUT_PAY, TOD_STAMP(SYSDATE) RS_UPDATE,USR_RS_IN_PAY
                                                                  FROM (SELECT USR_RS_NO,USR_RS_IN_PAY,USR_RS_OUT_PAY,SUM(USR_RS_IN_PAY+USR_RS_OUT_PAY) OVER(ORDER BY USR_RS_NO) + -4500SR
                                                                  FROM USER_CASH_RESERVE_H B
                                                                  WHERE USR_NO=1234 AND USR_RS_STATE=0)
                                                                  WHERE USR_RS_OUT_PAY<>CASE WHEN SR<=0 THEN USR_RS_IN_PAY*-1 ELSE LEAST((USR_RS_IN_PAY-SR)*-1,0) END
                                                                  )WHERE A.USR_RS_NO=USR_RS_NO)
        WHERE USR_RS_NO IN (SELECT USR_RS_NO
                            FROM (SELECT USR_RS_NO,USR_RS_IN_PAY,USR_RS_OUT_PAY,SUM(USR_RS_IN_PAY+USR_RS_OUT_PAY) OVER(ORDER BY USR_RS_NO) + -4500 SR
                                 FROM USER_CASH_RESERVE_H B
                                  WHERE USR_NO=1234 AND USR_RS_STATE=0)
                            WHERE USR_RS_OUT_PAY<>CASE WHEN SR<=0 THEN USR_RS_IN_PAY*-1 ELSE LEAST((USR_RS_IN_PAY-SR)*-1,0) END);