Master Table의 오래된 데이터를 삭제하려고보니, 엮여있는 테이블의 참조키(foreign key) 제약 조건 때문에 DELETE문에 JOIN을 사용하려고 한다.
DELETE
FROM TB_SALE_CUST
WHERE STATUS_CD = '07' AND SALE_DT <= TO_CHAR(CURRENT_DATE - INTERVAL '3 MONTH','YYYYMMDD')
# USING
PostgreSQL에서는 USING keyword를 사용한다.
WHERE절에서 JOIN 조건을 작성해준다.
DELETE FROM t1
USING t2
WHERE t1.id = t2.id
DELETE
FROM TB_RECV_AMT A
USING TB_SALE_CUST B
WHERE A.SL_KEY = B.SL_KEY
AND B.STATUS_CD = '07'
AND B.SALE_DT <= TO_CHAR(CURRENT_DATE - INTERVAL '3 MONTH','YYYYMMDD')
# 참고
https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-delete-join/
PostgreSQL DELETE JOIN - How to Emulate it Correctly
Summary: in this tutorial, you will learn how to use the PostgreSQL DELETE statement to emulate delete join operations. Introduction to PostgreSQL DELETE statement with USING clause PostgreSQL doesn’t support the DELETE JOIN statement. However, it does s
www.postgresqltutorial.com
728x90
'DB > PostgreSQL' 카테고리의 다른 글
[Postgresql] 영업일(business day)구하기 (1) | 2023.11.22 |
---|---|
[PostgreSQL] 데이터 기간 조회(당일, 주간, 월간, 연간) (0) | 2023.10.04 |
[PostgreSQL] Update Join (0) | 2023.01.05 |
[PostgreSQL] 날짜/시간 관련 함수 1 (2) | 2022.09.14 |
[postgreSql] GROUPING SETS/ROLLUP (0) | 2022.05.24 |