안녕하세요.. 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() 같은 함수때문에 그런것 같은데요.. 정확한 이유는 모르겠습니다.
이렇게 저렇게 해봐도 안되는데.. 어떤 점이 문제일까요.... 어렵네요..
답변 부탁드리겠습니다.
굳이 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 ) ;