style24.doc/산출물/4.구축/데이터처리/08.GNB탭생성(TB_GNB_TAB).sql

TRUNCATE TABLE TB_GNB_TAB_PREVIEW;

-- 공통GNB
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '여성', 'C', 1100, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 1, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '남성', 'C', 1200, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 2, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '유아동', 'C', 1300, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 3, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '골프', 'C', 1500, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 4, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '리빙', 'C', 1600, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 5, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '베스트', 'L', NULL, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 6, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '핫딜', 'L', NULL, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 7, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '기획전', 'L', NULL, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 8, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '총알배송', 'L', NULL, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 9, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '아울렛', 'O', NULL, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 10, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '룩북', 'L', NULL, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 11, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '이벤트/혜택', 'L', NULL, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 12, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '선물하기', 'L', NULL, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 13, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '신상품', 'L', NULL, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 14, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '다다익선', 'L', NULL, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 15, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '스타일리포트', 'L', NULL, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 16, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'C', '프리오더', 'L', NULL, NULL, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 17, NOW(), 1, NOW());

-- 브랜드GNB (TBJ)
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'B', '룩북', 'L', NULL, 24555, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 2, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'B', '20S/S시즌오프', 'L', NULL, 24555, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 3, NOW(), 1, NOW());
INSERT INTO TB_GNB_TAB_PREVIEW VALUES (NULL, 'B', '이벤트', 'L', NULL, 24555, '#', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, 'Y', 4, NOW(), 1, NOW());

SELECT * FROM TB_GNB_TAB_PREVIEW;

INSERT INTO TB_GNB_TAB
SELECT NULL
     , A.*
FROM   TB_GNB_TAB_PREVIEW A
;

-- 공통GNB > 브랜드
DELETE FROM TB_CONTENTS_PREVIEW WHERE CONTENTS_LOC = 'STAB001';

INSERT INTO TB_CONTENTS_PREVIEW (PRE_CONTENTS_SQ, CATE_NO, CONTENTS_LOC, DISP_STDT, DISP_EDDT, DISP_ORD, STR_VAR1, STR_TITLE1, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
VALUES (NULL, 1700, 'STAB001', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 1, '24501', '캐주얼', 'Y', 1, NOW(), 1, NOW())
;
INSERT INTO TB_CONTENTS_PREVIEW (PRE_CONTENTS_SQ, CATE_NO, CONTENTS_LOC, DISP_STDT, DISP_EDDT, DISP_ORD, STR_VAR1, STR_TITLE1, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
VALUES (NULL, 1700, 'STAB001', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 2, '24555', '캐주얼', 'Y', 1, NOW(), 1, NOW())
;
INSERT INTO TB_CONTENTS_PREVIEW (PRE_CONTENTS_SQ, CATE_NO, CONTENTS_LOC, DISP_STDT, DISP_EDDT, DISP_ORD, STR_VAR1, STR_TITLE1, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
VALUES (NULL, 1700, 'STAB001', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 3, '24556', '캐주얼', 'Y', 1, NOW(), 1, NOW())
;
INSERT INTO TB_CONTENTS_PREVIEW (PRE_CONTENTS_SQ, CATE_NO, CONTENTS_LOC, DISP_STDT, DISP_EDDT, DISP_ORD, STR_VAR1, STR_TITLE1, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
VALUES (NULL, 1700, 'STAB001', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 4, '24427', '캐주얼', 'Y', 1, NOW(), 1, NOW())
;
INSERT INTO TB_CONTENTS_PREVIEW (PRE_CONTENTS_SQ, CATE_NO, CONTENTS_LOC, DISP_STDT, DISP_EDDT, DISP_ORD, STR_VAR1, STR_TITLE1, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
VALUES (NULL, 1700, 'STAB001', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 5, '21671', '캐주얼', 'Y', 1, NOW(), 1, NOW())
;

INSERT INTO TB_CONTENTS_PREVIEW (PRE_CONTENTS_SQ, CATE_NO, CONTENTS_LOC, DISP_STDT, DISP_EDDT, DISP_ORD, STR_VAR1, STR_TITLE1, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
VALUES (NULL, 1700, 'STAB001', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 6, '25778', '골프', 'Y', 1, NOW(), 1, NOW())
;
INSERT INTO TB_CONTENTS_PREVIEW (PRE_CONTENTS_SQ, CATE_NO, CONTENTS_LOC, DISP_STDT, DISP_EDDT, DISP_ORD, STR_VAR1, STR_TITLE1, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
VALUES (NULL, 1700, 'STAB001', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 7, '26492', '골프', 'Y', 1, NOW(), 1, NOW())
;

INSERT INTO TB_CONTENTS_PREVIEW (PRE_CONTENTS_SQ, CATE_NO, CONTENTS_LOC, DISP_STDT, DISP_EDDT, DISP_ORD, STR_VAR1, STR_TITLE1, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
VALUES (NULL, 1700, 'STAB001', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 8, '19706', '키즈', 'Y', 1, NOW(), 1, NOW())
;
INSERT INTO TB_CONTENTS_PREVIEW (PRE_CONTENTS_SQ, CATE_NO, CONTENTS_LOC, DISP_STDT, DISP_EDDT, DISP_ORD, STR_VAR1, STR_TITLE1, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
VALUES (NULL, 1700, 'STAB001', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 9, '22815', '키즈', 'Y', 1, NOW(), 1, NOW())
;
INSERT INTO TB_CONTENTS_PREVIEW (PRE_CONTENTS_SQ, CATE_NO, CONTENTS_LOC, DISP_STDT, DISP_EDDT, DISP_ORD, STR_VAR1, STR_TITLE1, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
VALUES (NULL, 1700, 'STAB001', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 10, '26236', '키즈', 'Y', 1, NOW(), 1, NOW())
;
INSERT INTO TB_CONTENTS_PREVIEW (PRE_CONTENTS_SQ, CATE_NO, CONTENTS_LOC, DISP_STDT, DISP_EDDT, DISP_ORD, STR_VAR1, STR_TITLE1, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
VALUES (NULL, 1700, 'STAB001', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 11, '25664', '키즈', 'Y', 1, NOW(), 1, NOW())
;
INSERT INTO TB_CONTENTS_PREVIEW (PRE_CONTENTS_SQ, CATE_NO, CONTENTS_LOC, DISP_STDT, DISP_EDDT, DISP_ORD, STR_VAR1, STR_TITLE1, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
VALUES (NULL, 1700, 'STAB001', NOW(), DATE_ADD(NOW(), INTERVAL 3 MONTH), 12, '442', '키즈', 'Y', 1, NOW(), 1, NOW())
;

COMMIT;

DELETE FROM TB_CONTENTS WHERE CONTENTS_LOC = 'STAB001';

INSERT INTO TB_CONTENTS (CATE_NO, CONTENTS_LOC, CONTENTS_TYPE, DISP_STDT, DISP_EDDT, DISP_ORD, IMG_PATH1, IMG_PATH2, IMG_PATH3, IMG_PATH4, IMG_PATH5, IMG_PATH6, IMG_PATH7, IMG_PATH8, STR_VAR1, STR_VAR2, STR_VAR3, STR_VAR4, STR_VAR5, STR_VAR6, STR_VAR7, STR_VAR8, STR_TITLE1, STR_TITLE2, STR_TITLE3, STR_TITLE4, SUB_TEXT1, SUB_TEXT2, SUB_TEXT3, SUB_TEXT4, HTML, BRAND_GROUP_NO, BRAND_GROUP_IMG, MD_TITLE, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT)
SELECT                   CATE_NO, CONTENTS_LOC, CONTENTS_TYPE, DISP_STDT, DISP_EDDT, DISP_ORD, IMG_PATH1, IMG_PATH2, IMG_PATH3, IMG_PATH4, IMG_PATH5, IMG_PATH6, IMG_PATH7, IMG_PATH8, STR_VAR1, STR_VAR2, STR_VAR3, STR_VAR4, STR_VAR5, STR_VAR6, STR_VAR7, STR_VAR8, STR_TITLE1, STR_TITLE2, STR_TITLE3, STR_TITLE4, SUB_TEXT1, SUB_TEXT2, SUB_TEXT3, SUB_TEXT4, HTML, BRAND_GROUP_NO, BRAND_GROUP_IMG, MD_TITLE, USE_YN, REG_NO, REG_DT, UPD_NO, UPD_DT
FROM   TB_CONTENTS_PREVIEW
WHERE  CONTENTS_LOC = 'STAB001'
;

UPDATE TB_CONTENTS_PREVIEW A
SET    CONTENTS_SQ = (SELECT CONTENTS_SQ
                      FROM   tb_contents
                      WHERE  CONTENTS_LOC = A.CONTENTS_LOC
                      AND    STR_VAR1 = A.STR_VAR1
                     )
WHERE  CONTENTS_LOC = 'STAB001'
;

COMMIT;

-- GNB탭 - 브랜드그룹 로고이미지 수정
UPDATE TB_BRAND_GROUP SET RGB_CD = '436564', LOGO_FILE_NM = '/contents/brand/BRAND_24501_20210315125924.png' WHERE BRAND_GROUP_NO = 24501;
UPDATE TB_BRAND_GROUP SET RGB_CD = '222222', LOGO_FILE_NM = '/contents/brand/BRAND_24555_20210315125956.png' WHERE BRAND_GROUP_NO = 24555;
UPDATE TB_BRAND_GROUP SET RGB_CD = '222222', LOGO_FILE_NM = '/contents/brand/BRAND_24556_20210315131611.png' WHERE BRAND_GROUP_NO = 24556;
UPDATE TB_BRAND_GROUP SET RGB_CD = '222222', LOGO_FILE_NM = '/contents/brand/BRAND_24427_20210315130647.png' WHERE BRAND_GROUP_NO = 24427;
UPDATE TB_BRAND_GROUP SET RGB_CD = '222222', LOGO_FILE_NM = '/contents/brand/BRAND_21671_20210315130915.png' WHERE BRAND_GROUP_NO = 21671;
UPDATE TB_BRAND_GROUP SET RGB_CD = '222222', LOGO_FILE_NM = '/contents/brand/BRAND_25778_20210315125743.png' WHERE BRAND_GROUP_NO = 25778;
UPDATE TB_BRAND_GROUP SET RGB_CD = '222222', LOGO_FILE_NM = '/contents/brand/BRAND_26492_20210315125214.png' WHERE BRAND_GROUP_NO = 26492;
UPDATE TB_BRAND_GROUP SET RGB_CD = '222222', LOGO_FILE_NM = '/contents/brand/BRAND_19706_20210315131237.png' WHERE BRAND_GROUP_NO = 19706;
UPDATE TB_BRAND_GROUP SET RGB_CD = '222222', LOGO_FILE_NM = '/contents/brand/BRAND_22815_20210315132753.png' WHERE BRAND_GROUP_NO = 22815;
UPDATE TB_BRAND_GROUP SET RGB_CD = '222222', LOGO_FILE_NM = '/contents/brand/BRAND_26236_20210315130715.png' WHERE BRAND_GROUP_NO = 26236;
UPDATE TB_BRAND_GROUP SET RGB_CD = '222222', LOGO_FILE_NM = '/contents/brand/BRAND_25664_20210315132709.png' WHERE BRAND_GROUP_NO = 25664;
UPDATE TB_BRAND_GROUP SET RGB_CD = '222222', LOGO_FILE_NM = '/contents/brand/BRAND_442_20210315132616.png' WHERE BRAND_GROUP_NO = 442;

COMMIT;