코드 매핑작업; a_code에 해당하는 b_code를 매핑테이블을 활용해 넣어주기

코드테이블

CREATE TABLE public.t_comm_cd (
	comm_code varchar(20) NOT NULL,
	category varchar(20) NULL,
	comm_nm varchar(200) NULL,
	use bool NULL,
	p_code varchar(20) NULL,
	reg_date timestamp(6) NULL,
	upd_date timestamp(6) NULL,
	eng_nm varchar(20) NULL,
	sort_ordr int4 NULL,
	CONSTRAINT t_comm_cd_pk PRIMARY KEY (comm_code),
	CONSTRAINT t_comm_cd_fk1 FOREIGN KEY (category) REFERENCES public.t_category_cd(ctgry_code)
);
CREATE INDEX t_comm_cd_nk1 ON public.t_comm_cd USING btree (category, comm_code);
CREATE INDEX t_comm_cd_nk2 ON public.t_comm_cd USING btree (p_code);

 

 

매핑테이블

CREATE TABLE public.t_cd_map (
	mapng_purps_cd varchar(20) NOT NULL,
	sourc_code varchar NOT NULL,
	mapng_code varchar NOT NULL,
	mapng_type_cd varchar(20) NULL,
	reg_date timestamp(6) NULL DEFAULT to_char(now(), 'yyyy-mm-dd HH24:MI:SS'::text)::timestamp without time zone,
	upd_date timestamp(6) NULL DEFAULT to_char(now(), 'yyyy-mm-dd HH24:MI:SS'::text)::timestamp without time zone,
	CONSTRAINT t_cd_map_pk PRIMARY KEY (mapng_purps_cd, sourc_code, mapng_code)
);

 

 

 

데이터

sourc_code와 mapng_code는 공통코드테이블에 카테고리를 다르게 하여 넣어줬음

 

 

 

update문 매핑

UPDATE 
table_a
SET wst_type_cd = (select mapng_code from t_cd_map tcm where sourc_code = 'RCY0503' and mapng_purps_cd = 'MP00102')
WHERE fclt_sn='25'and fclt_year='2021'and fclt_mth='11'and sgg_cd='23040'

 

 

 

insert문 매핑

  INSERT
  INTO table_a(
			sts_wst_type_cd
			,wst_type_cd
        ) VALUES 
        	<foreach collection="data" item="data"   separator=",">
			 	( 
			 	#{data.sts_wst_type_cd}
                , (select mapng_code 
                   from t_cd_map 
                   where sourc_code = #{data.sts_wst_type_cd} and mapng_purps_cd = #{data.mapng_purps_cd})
                )
			</foreach>

난 이렇게 썼지만 간단한 insert문으로 바꾸면 아래와 같겠다..

  INSERT
  INTO table_a(
			sts_wst_type_cd
			,wst_type_cd
        ) VALUES 
			 	#{sts_wst_type_cd}
                , (select mapng_code 
                   from t_cd_map 
                   where sourc_code = #{sts_wst_type_cd} and mapng_purps_cd = #{mapng_purps_cd})
                )