merge 문의 드립니다.. 0 4 4,000

by 손님 merge [2012.07.09 08:55:30]


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

이렇게 뜨네요... 몇일째 해결을 못하고있습니다. 도와주세요...

by 이재현 [2012.07.09 13:33:56]
 
 


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  
  );

 

 

by 손님 [2012.07.09 14:11:55]

오우 감사합니다!!!

근데 45번째줄 마지막에 SU는 왜 미싱키워드로 나오는걸까요??ㅠㅠ

아아 어렵다..

by 손님 [2012.07.09 17:59:05]
 
에러 원인 
 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  
 ); 


by 마농 [2012.07.10 09:35:04]

조건절에 알리아스를 주면 이상하죠...
ON 절 맨 끝에 su 빼세요.
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입