데이터베이스 질문과 답변
안녕하세요.. UPDATE 관련 질문 드리겠습니다.
아래와 같은 쿼리문이 있는데요..
UPDATE /*+ bypass_ujvc */
(SELECT /*+ lead(b) rowid(a) */
A.CHRENO
, A.CHRMDT
, A.CHRTIM
, A.CHRFLG
FROM TEST_TB A
, (SELECT
CHRENO
, ROW_NUMBER() OVER(ORDER BY CHRMDT DESC, CHRTIM DESC) AS RNUM
, ROWID
FROM TEST_TB
WHERE CHRENO = 'TEST0000021'
AND CHRKND = 'O'
) B
WHERE A.ROWID = B.ROWID
AND B.RNUM = 1
)
SET CHRFLG = 'Y';
TEST_TB의 테이블의 KEY는
CHRENO, CHRMDT, CHRTIM 입니다.
CHRMDT, CHRTIM은 날짜와 시간이구요..
이력성 테이블이기 때문에
CHRENO가 주어진 상태에서 CHRMDT, CHRTIM값이 최대인걸 찾아서 업데이트 하려고
한번 저렇게 해봤는데요
ORA-01779: 키-보존된것이 아닌 테이블로 대응한 열을 수정할 수 없습니다
에러가 발생합니다...
좀 찾아보니까 ROW_NUMBER() 같은 함수때문에 그런것 같은데요.. 정확한 이유는 모르겠습니다.
이렇게 저렇게 해봐도 안되는데.. 어떤 점이 문제일까요.... 어렵네요..
답변 부탁드리겠습니다.
http://www.gurubee.net/article/56972 <- 요 글 참고해보시면 좋을 것 같아요.
굳이 merge까지 사용할 필요 없어보이는데 확인해보세요.
--확인해보시고
SELECT *
FROM TEST_TB
WHERE ROWID IN (SELECT RID
FROM (SELECT ROW_NUMBER() OVER (ORDER BY CHRMDT DESC, CHRTIM DESC) AS RNUM
, ROWID RID
FROM TEST_TB
WHERE CHRENO = 'TEST0000021'
AND CHRKND = 'O')
WHERE RNUM = 1)
--맞으면 UPDATE
UPDATE TEST_TB
SET CHRFLG = 'Y'
WHERE ROWID IN (SELECT RID
FROM (SELECT ROW_NUMBER() OVER (ORDER BY CHRMDT DESC, CHRTIM DESC) AS RNUM
, ROWID RID
FROM TEST_TB
WHERE CHRENO = 'TEST0000021'
AND CHRKND = 'O')
WHERE RNUM = 1)
뷰 업데이트 시 키보존이라는 의미는
업데이트 대상인 A 와 조인되는 B 의 조인키가 유니크 해야 한다는 것입니다.
물론 논리적으로 보면 rowid 도 유니크하고 rn=1 도 유니크한 조건이긴 하지만.
이런 논리적인 유니크 말고 물리적인 유니크 가지고만 판단합니다.
조인키가 PK 나 UK 가 설정된 항목인지를 가지고 판단합니다.
그래서 키보존 에러가 나는 거구요.
위와 같이 논리적으로 유니크함이 확실할 경우에 한해서.
10G 까지는 힌트를 통해 에러를 무력화 시킬 수 있었는데.
11G 부터는 해당 힌트를 사용할 수 없게 되었습니다.
MERGE 구문을 이용하여 해결할 수 있는데.
다만 위의 경우는 업데이트 되는 값이 조인 대상에서 가져오는 값이 아닌 상수값이므로
굳이 조인이 필요 없습니다.
단순하게 서브쿼리 조건을 이용하는것이 좋을 듯 하네요.
MERGE INTO test_tb a
USING
(
SELECT rid
FROM (SELECT ROWID rid
, ROW_NUMBER() OVER(ORDER BY chrmdt DESC, chrtim DESC) rn
FROM test_tb
WHERE chreno = 'TEST0000021'
AND chrknd = 'O'
)
WHERE rn = 1
) b
ON a.ROWID = b.rid
WHEN MATCHED THEN
UPDATE SET chrflg = 'Y'
;
UPDATE test_tb
SET chrflg = 'Y'
WHERE ROWID = (SELECT rid
FROM (SELECT ROWID rid
, ROW_NUMBER() OVER(ORDER BY chrmdt DESC, chrtim DESC) rn
FROM test_tb
WHERE chreno = 'TEST0000021'
AND chrknd = 'O'
)
WHERE rn = 1
)
;
헐.. 답변 채택이 한분밖에 안되는군요...다 하려고 했는데..
답변주신분들 내용 확인하면서 이것저것 많이 배웠습니다.
모두 감사합니다~
) 버튼을 클릭하여 작성 하시면 됩니다.