데이터베이스 질문과 답변
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
이렇게 뜨네요... 몇일째 해결을 못하고있습니다. 도와주세요...
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 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
);
오우 감사합니다!!!
근데 45번째줄 마지막에 SU는 왜 미싱키워드로 나오는걸까요??ㅠㅠ
아아 어렵다..
에러 원인
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 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
);
조건절에 알리아스를 주면 이상하죠...
ON 절 맨 끝에 su 빼세요.
) 버튼을 클릭하여 작성 하시면 됩니다.