동일한 테이블의 다른 컬럼 참조하여 업데이트하고싶을 때
- 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;
