데이터베이스 질문과 답변
MERGE INTO TB_SUNJININFO TT
USING TB_SUNJININFO_UPDATE SU
ON (TT. CSMNO = SU. CSMNO and TT. CHM = SU.CHM and
TT.CHMWORKTO is null)
WHEN MATCHED THEN
UPDATE SET
TT.CHM = SU.CHM
,TT.CHMTLNO = SU.CHMTLNO
,TT.CHMFAXNO = SU.CHMFAXNO
,TT.CHMHP = SU.CHMHP
,TT.CHMSEX = SU.CHMSEX
,TT.CHMAGE = SU.CHMAGE
,TT.CHMRESP = SU.CHMRESP
,TT.CHMWORKFROM = SU.CHMWORKFROM
,TT.CHMIMPRESSION = SU.CHMIMPRESSION
,TT.CHMCONTENT = SU.CHMIMPRESSION
,TT.VISTIMPRESSION = SU.VISTIMPRESSION
,TT.COMPIMPRESSION = SU.COMPIMPRESSION
,TT.ETCINFO = SU.ETCINFO
,TT.COMPEMPNM1 = SU.COMPEMPNM1
,TT.COMPEMPNM2 = SU.COMPEMPNM2
,TT.UPDDATE = SYSDATE
,TT.CHMRESPGRP = SU.CHMRESPGRP
WHEN NOT MATCHED THEN
INSERT
(SUNJINNO,CSMNO,CHM,CHMTLNO,CHMFAXNO,CHMHP,CHMSEX,CHMAGE,CHMRESP,CHMWORKFROM,CHMIMPRESSION,VISTIMPRESSION,COMPIMPRESSION,ETCINFO,COMPEMPNM1,COMPEMPNM2,UPDDATE,CHMRESPGRP)
VALUES(
86000000+SEQ_SUNJINNO.NEXTVAL
,SU.CSMNO
,SU.CHM
,SU.CHMTLNO
,SU.CHMFAXNO
,SU.CHMHP
,SU.CHMSEX
,SU.CHMAGE
,SU.CHMRESP
,SU.CHMWORKFROM
,SU.CHMIMPRESSION
,SU.VISTIMPRESSION
,SU.COMPIMPRESSION
,SU.ETCINFO
,SU.COMPEMPNM1
,SU.COMPEMPNM2
,SYSDATE
,SU.CHMRESPGRP
);
현재 쿼리입니다. 키값은 SUNJININO 하나이고 나머지는 모두 중복가능합니다.
에러는 ORA-38104: Columns referenced in the ON Clause cannot be updated: "TT"."CHM"
이것과 TT.CHM 구문을 제거하면
ORA-30926: unable to get a stable set of rows in the source tables
이렇게 뜨네요... 몇일째 해결을 못하고있습니다. 도와주세요...
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 | MERGE INTO TB_SUNJININFO TT USING ( SELECT /*+ FULL( A ) USE_HASH( A B ) */ B.* FROM TB_SUNJININFO A , (SELECT MAX( B.CHM ) AS CHM , MAX( B.CHMTLNO ) AS CHMTLNO , MAX( B.CHMFAXNO ) AS CHMFAXNO , MAX(B.CHMHP ) AS CHMHP , MAX(B.CHMSEX ) AS CHMSEX , MAX(B.CHMAGE ) AS CHMAGE , MAX(B.CHMRESP ) AS CHMRESP , MAX(B.CHMWORKFROM ) AS CHMWORKFROM , MAX(B.CHMIMPRESSION ) AS CHMIMPRESSION , MAX(B.CHMCONTENT ) AS CHMCONTENT , MAX(B.VISTIMPRESSION ) AS VISTIMPRESSION , MAX(B.COMPIMPRESSION ) AS COMPIMPRESSION , MAX(B.ETCINFO ) AS ETCINFO , MAX(B.COMPEMPNM1 ) AS COMPEMPNM1 , MAX(B.COMPEMPNM2 ) AS COMPEMPNM2 , MAX(B.UPDDATE ) AS UPDDATE , MAX(B.CHMRESPGRP ) AS CHMRESPGRP FROM TB_SUNJININFO_UPDATE B GROUP BY B.CHM ,B.CHMTLNO ,B.CHMFAXNO ,B.CHMHP ,B.CHMSEX ,B.CHMAGE ,B.CHMRESP ,B.CHMWORKFROM ,B.CHMIMPRESSION ,B.CHMCONTENT ,B.VISTIMPRESSION ,B.COMPIMPRESSION ,B.ETCINFO ,B.COMPEMPNM1 ,B.COMPEMPNM2 ,B.UPDDATE ,B.CHMRESPGRP ) B WHERE A.CHMWORKTO is null AND A.CSMNO = B.CSMNO AND A.CHM = B.CHM ) SU ON (TT. CSMNO = SU. CSMNO and TT. CHM = SU.CHM ) SUWHEN MATCHED THEN UPDATE SET TT.CHMFAXNO = SU.CHMFAXNO ,TT.CHMHP = SU.CHMHP ,TT.CHMSEX = SU.CHMSEX ,TT.CHMAGE = SU.CHMAGE ,TT.CHMRESP = SU.CHMRESP ,TT.CHMWORKFROM = SU.CHMWORKFROM ,TT.CHMIMPRESSION = SU.CHMIMPRESSION ,TT.CHMCONTENT = SU.CHMIMPRESSION ,TT.VISTIMPRESSION = SU.VISTIMPRESSION ,TT.COMPIMPRESSION = SU.COMPIMPRESSION ,TT.ETCINFO = SU.ETCINFO ,TT.COMPEMPNM1 = SU.COMPEMPNM1 ,TT.COMPEMPNM2 = SU.COMPEMPNM2 ,TT.UPDDATE = SYSDATE ,TT.CHMRESPGRP = SU.CHMRESPGRPWHEN NOT MATCHED THENINSERT(SUNJINNO,CSMNO,CHM,CHMTLNO,CHMFAXNO,CHMHP,CHMSEX,CHMAGE,CHMRESP,CHMWORKFROM,CHMIMPRESSION,VISTIMPRESSION,COMPIMPRESSION,ETCINFO,COMPEMPNM1,COMPEMPNM2,UPDDATE,CHMRESPGRP) VALUES( 86000000+SEQ_SUNJINNO.NEXTVAL ,SU.CSMNO ,SU.CHM ,SU.CHMTLNO ,SU.CHMFAXNO ,SU.CHMHP ,SU.CHMSEX ,SU.CHMAGE ,SU.CHMRESP ,SU.CHMWORKFROM ,SU.CHMIMPRESSION ,SU.VISTIMPRESSION ,SU.COMPIMPRESSION ,SU.ETCINFO ,SU.COMPEMPNM1 ,SU.COMPEMPNM2 ,SYSDATE ,SU.CHMRESPGRP ); |
오우 감사합니다!!!
근데 45번째줄 마지막에 SU는 왜 미싱키워드로 나오는걸까요??ㅠㅠ
아아 어렵다..
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 | 에러 원인 1. TB_SUNJININFO 절에 중복데이타로 인서트가 발생해서 에러가 발생한것입니다. : 맥스 함수로 해결했지만 우선안에 절을 실행해서 데이타가 맞나 확인하시구 실행하셔요. 2. ON 조건절은업데이트 불가능합니다. 3. 미싱키워드 : 대충 복사해서 만들엇고, 실행해보지 않아서 그렇습니다. 심텍스에러 입니다. MERGE INTO TB_SUNJININFO TT USING ( SELECT /*+ FULL( A ) USE_HASH( A B ) */ B.* FROM TB_SUNJININFO A , (SELECT MAX( B.CHM ) AS CHM , MAX( B.CHMTLNO ) AS CHMTLNO , MAX( B.CHMFAXNO ) AS CHMFAXNO , MAX(B.CHMHP ) AS CHMHP , MAX(B.CHMSEX ) AS CHMSEX , MAX(B.CHMAGE ) AS CHMAGE , MAX(B.CHMRESP ) AS CHMRESP , MAX(B.CHMWORKFROM ) AS CHMWORKFROM , MAX(B.CHMIMPRESSION ) AS CHMIMPRESSION , MAX(B.CHMCONTENT ) AS CHMCONTENT , MAX(B.VISTIMPRESSION ) AS VISTIMPRESSION , MAX(B.COMPIMPRESSION ) AS COMPIMPRESSION , MAX(B.ETCINFO ) AS ETCINFO , MAX(B.COMPEMPNM1 ) AS COMPEMPNM1 , MAX(B.COMPEMPNM2 ) AS COMPEMPNM2 -- , MAX(B.UPDDATE ) AS UPDDATE , MAX(B.CHMRESPGRP ) AS CHMRESPGRP FROM TB_SUNJININFO_UPDATE B GROUP BY B.CHM ,B.CHMTLNO ,B.CHMFAXNO ,B.CHMHP ,B.CHMSEX ,B.CHMAGE ,B.CHMRESP ,B.CHMWORKFROM ,B.CHMIMPRESSION ,B.CHMCONTENT ,B.VISTIMPRESSION ,B.COMPIMPRESSION ,B.ETCINFO ,B.COMPEMPNM1 ,B.COMPEMPNM2 -- ,B.UPDDATE ,B.CHMRESPGRP ) B WHERE A.CHMWORKTO is null AND A.CSMNO = B.CSMNO AND A.CHM = B.CHM ) SU ON (TT. CSMNO = SU. CSMNO and TT. CHM = SU.CHM ) SU WHEN MATCHED THEN UPDATE SET TT.CHMFAXNO = SU.CHMFAXNO ,TT.CHMHP = SU.CHMHP ,TT.CHMSEX = SU.CHMSEX ,TT.CHMAGE = SU.CHMAGE ,TT.CHMRESP = SU.CHMRESP ,TT.CHMWORKFROM = SU.CHMWORKFROM ,TT.CHMIMPRESSION = SU.CHMIMPRESSION ,TT.CHMCONTENT = SU.CHMIMPRESSION ,TT.VISTIMPRESSION = SU.VISTIMPRESSION ,TT.COMPIMPRESSION = SU.COMPIMPRESSION ,TT.ETCINFO = SU.ETCINFO ,TT.COMPEMPNM1 = SU.COMPEMPNM1 ,TT.COMPEMPNM2 = SU.COMPEMPNM2 ,TT.UPDDATE = SYSDATE ,TT.CHMRESPGRP = SU.CHMRESPGRP WHEN NOT MATCHED THENINSERT(SUNJINNO,CSMNO,CHM,CHMTLNO,CHMFAXNO,CHMHP,CHMSEX,CHMAGE,CHMRESP,CHMWORKFROM,CHMIMPRESSION,VISTIMPRESSION,COMPIMPRESSION,ETCINFO,COMPEMPNM1,COMPEMPNM2,UPDDATE,CHMRESPGRP) VALUES( 86000000+SEQ_SUNJINNO.NEXTVAL ,SU.CSMNO ,SU.CHM ,SU.CHMTLNO ,SU.CHMFAXNO ,SU.CHMHP ,SU.CHMSEX ,SU.CHMAGE ,SU.CHMRESP ,SU.CHMWORKFROM ,SU.CHMIMPRESSION ,SU.VISTIMPRESSION ,SU.COMPIMPRESSION ,SU.ETCINFO ,SU.COMPEMPNM1 ,SU.COMPEMPNM2 ,SYSDATE ,SU.CHMRESPGRP ); |
조건절에 알리아스를 주면 이상하죠...
ON 절 맨 끝에 su 빼세요.
) 버튼을 클릭하여 작성 하시면 됩니다.