[SQL] INSERT, UPDATE 모음

// INSERT문 - 다른 USER의 테이블 데이터 복사에 넣기
INSERT INTO 트윈테이블1
SELECT * FROM 스키마2.트윈테이블2;
# 응용
INSERT INTO 트윈테이블1
SELECT * FROM 스키마2.트윈테이블2;
WHERE cd_type_id||cd NOT IN (SELECT cd_type_id||cd FROM T_CODE );
-- PK가 composite key일 경우 where조건을 간단하게 바꾼경우
# 특정 컬럼만 IMPORT
INSERT INTO 트윈테이블1
(특정컬럼1, 특정컬럼2)
SELECT 특정컬럼1, 특정컬럼2 FROM 스키마2.트윈테이블2;

 

// UPDATE문 - 두 개 테이블 조인하여 UPDATE
UPDATE LS_ST_INDEX_C SET
LS_ST_INDEX_C.EZONE_YN = (SELECT LS_ST_INDEX.EZONE_YN FROM LS_ST_INDEX WHERE LS_ST_INDEX_C.DATA_CODE = LS_ST_INDEX.INDEX_CODE)
, LS_ST_INDEX_C.PROVINCE_YN = (SELECT LS_ST_INDEX.PROVINCE_YN FROM LS_ST_INDEX WHERE LS_ST_INDEX_C.DATA_CODE = LS_ST_INDEX.INDEX_CODE)
, LS_ST_INDEX_C.CITY_YN = (SELECT LS_ST_INDEX.CITY_YN FROM LS_ST_INDEX WHERE LS_ST_INDEX_C.DATA_CODE = LS_ST_INDEX.INDEX_CODE)
, LS_ST_INDEX_C.UNIT_CODE = (SELECT LS_ST_INDEX.UNIT_CODE FROM LS_ST_INDEX WHERE LS_ST_INDEX_C.DATA_CODE = LS_ST_INDEX.INDEX_CODE)
, LS_ST_INDEX_C.END_YN = 'N'
WHERE LS_ST_INDEX_C.DATA_CODE IN (SELECT LS_ST_INDEX.INDEX_CODE FROM LS_ST_INDEX WHERE LS_ST_INDEX_C.DATA_CODE = LS_ST_INDEX.INDEX_CODE);