UPDATE 문 UPDATE FROM 절 주의사항

UPDATE문의 자료를 변경할 때 주의할 점으로 시점의 문제가 있다.

 

예를들어 TEST_500 테이블에 다음의 자료가 있을경우 

(TEST_500 원본)

acadid	amt_gbn	in_amt	out_amt	SRL
111	현금	500	0	1
111	현금	0	500	2
111	현금	700	600	3
111	카드	0	600	4

 

 

 

다음 UPDATE문은  어떻게 될까?

 

UPDATE TEST_500
SET in_amt= in_amt+100, out_amt=out_amt+in_amt
WHERE SRL=1

 

결과는 다음과 같다  

(TEST_500 결과)

acadid	amt_gbn	in_amt	out_amt	SRL
111	현금	600	500	1
111	현금	0	500	2
111	현금	700	600	3
111	카드	0	600	4

 

 

UPDATE 시점의 in_amt = 500, out_amt=0을 기준으로 업데이트된다. 다시말해 반영된 값을 다시 불러오지 않는다는 것이다.

 

이런 문제는

UPDATE ~ FROM 절에서도 주의를 요한다.

 

잔액 테이블 TEST_REMAIN 이 다음과 같을 때   (TEST_500 원본) 테이블의 값을 누적하여 업데이트 한다면

 

ACADID	amt_gbn	remain
111	현금	0
111	카드	0

 

 

다음 문장은 1건만 반영되어 원하는 결과가 나올 수 없다. 

 

UPDATE a                         
  SET remain = isnull(remain, 0) + d.IN_AMT - d.OUT_AMT
  FROM TEST_REMAIN a inner join TEST_500 d on a.ACADID=d.ACADID and a.amt_gbn=d.amt_gbn
  AND d.SRL<=2;

 

 

ACADID	amt_gbn	remain
111	현금	500
111	카드	0

 

 

조인을 하여 UPDATE 할때 최초 FROM 절에서 대상 데이터을 정하고(원본데이터) 이 원본 데이터를 기준으로 여러 건의 업데이트를 처리한다.

여러건의 잔액 등을 누적해서 업데이트할 때 주의를 해야한다.

만약 잔액등을 최초원본을 업데이트되고 또 변경된 잔액을 다시 업데이트 하고자 한다면

그룹을 지어 SUM함수로 변경될 값을 합하여 한건으로 최초 원본잔액에 반영해야한다.

 

즉 다음과 같이 해야 원하는 결과를 얻을 수 있다.

 

  UPDATE a                         
  SET remain = isnull(remain, 0) + d.sum_in_amt - d.sum_out_amt
  FROM TEST_REMAIN a inner join
                 (SELECT ACADID, amt_gbn, SUM(in_amt) [sum_in_amt], sum(out_amt) [sum_out_amt]
                  FROM TEST_500
                  WHERE SRL<=2
                  GROUP BY acadid, amt_gbn
                  ) d  
                 on a.ACADID=d.ACADID and a.amt_gbn=d.amt_gbn

 

 

ACADID	amt_gbn	remain
111	현금	0
111	카드	0