mysql query tip

동일한 테이블의 다른 컬럼 참조하여 업데이트하고싶을 때

• update tb_ic_mso_prdt_itm_bas set prdt_ver = (select prdt_ver from tb_ic_mso_prdt_itm_bas where..) 이렇게 하면 다음 에러발생
  • Error Code: 1093. You can't specify target table 'tb_ic_mso_prdt_itm_bas' for update in FROM clause
• 그럴경우 아래와 같이 T로 한번 더 감싸주면 됨
  • update tb_ic_mso_prdt_itm_bas set prdt_ver = (select T.* from (select prdt_ver from tb_ic_mso_prdt_itm_bas where..) T)
• 여기서 T를 가상/임시 테이블이라 하던가? 암튼 oracle에서는 아래와 같이 with T로 사용가능
  • with T as(select sysdate from dual) select * from T
• example
  • 에러

    update tb_ic_contents_cntry_code_map A
    set major_logo_url = ( select major_logo_url from tb_ic_contents_cntry_code_map where ic_contents_set_id='com.lge.crawler.xml.tms.TmsEpgCrawler' and cntry_code='US') 
    where ic_contents_set_id='com.lge.crawler.xml.gn.epg.GnEpgCrawlerMX' and cntry_code='MX'

  • 정상

    update tb_ic_contents_cntry_code_map A
    set major_logo_url = ( select * from
                    ( select major_logo_url from tb_ic_contents_cntry_code_map where ic_contents_set_id='com.lge.crawler.xml.tms.TmsEpgCrawler' and cntry_code='US') temp
                  )
    where ic_contents_set_id='com.lge.crawler.xml.gn.epg.GnEpgCrawlerMX' and cntry_code='MX'

 

이런식으로 order by 할 때 순서를 각각 정할 수도 있다

SELECT * FROM tb_ic_contents_set_job_gr
ORDER BY CASE ic_contents_set_id
WHEN 'com.lge.meta.crawler.newepg.NewEpgCrawler' THEN 1
WHEN 'com.lge.meta.TVMao.TVMaoCrawler' THEN 2 
ELSE 3 END

 

배열대신 string으로 임시테이블 생성하는 방법

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.col, '*', temp.n(index역할)), '*', -1) CHAN_CODE
FROM (select '12345*23451*12345' as col) t CROSS JOIN 
(SELECT a.N + b.N * 10 + 1 as n FROM 
(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b ORDER BY n
) temp WHERE temp.n <= 1 + (LENGTH(t.col) - LENGTH(REPLACE(t.col, '*', '')));

 

배열대신 string사용하여 index로 접근하는 방법

select SUBSTRING_INDEX(SUBSTRING_INDEX(t.col, ',', 1(index, 1부터시작)), ',', -1) CHAN_CODE
FROM (select '12345,23451,12345' as col) t;

How can I simulate an array variable in MySQL?

UPDATE HESDP_IBS.TB_IC_CHAN_BAS A
SET LG_CHAN_GENRE = (
                                            SELECT REPLACE(LG_GENRE_CODE, ';', '')
                                            FROM (
                                                             SELECT chan_code
                                                                           , LG_GENRE_CODE
                                                                           , IF(@PART = AA.CHAN_CODE, @RNUM := @RNUM + 1, @RNUM := 1) AS score
                                                                                , @PART := AA.CHAN_CODE AS PART
                                                                             FROM (
                                                                                           SELECT SVC.CHAN_CODE
, SVC.CHAN_STD_NAME
, SVC.LG_GENRE_CODE
, COUNT(*) AS GENRE_COUNT
FROM HESDP_IBS.TB_IC_N_SVC_BAS SVC
WHERE 1=1
AND SVC.IC_CONTENTS_SET_ID = '$(contentsSetId)'
GROUP BY SVC.CHAN_STD_NAME, SVC.CHAN_CODE, SVC.LG_GENRE_CODE
ORDER BY CHAN_CODE, GENRE_COUNT
) AA,
(SELECT @PART := '', @RNUM := 0) CC
ORDER BY AA.CHAN_CODE, AA.GENRE_COUNT DESC
) T
WHERE T.score=1
AND A.chan_code=T.chan_code
AND A.IC_CONTENTS_SET_ID = '$(contentsSetId)'
)
WHERE A.IC_CONTENTS_SET_ID = '$(contentsSetId)'
AND (A.LG_CHAN_GENRE = '999' OR A.LG_CHAN_GENRE IS NULL)



DECLARE CONTINUE HANDLER FOR NOT FOUND SET end_of_rows(미리생성한변수) =TRUE ;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err =TRUE ;

DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE, @sqlerrno = MYSQL_ERRNO, @sqlerrmsg = MESSAGE_TEXT;
SET O_error_message = (CONCAT('PACKAGE_ERROR',' ',
Ifnull(L_function, '') ,' ',
IFNULL(@sqlstate, '') ,' ',
IFNULL(@sqlerrno, ''), ' ',
IFNULL(@sqlerrmsg, '')));
ROLLBACK;