[MySQL] WITH CTE로 INSERT / UPDATE / DELETE 처리하는 실전 예제 모음

MySQL 8.0부터 CTE(Common Table Expressions), 즉 WITH 구문을 지원하면서 복잡한 쿼리를 더 깔끔하고 유지보수하기 좋은 형태로 작성할 수 있게 되었다. 대부분은 SELECT에서만 쓰인다고 생각하지만, INSERT / UPDATE / DELETE에도 응용이 가능하다.

특히 트랜잭션 처리, 연산 결과 재활용, 조건 분기 처리에서 WITH CTE는 매우 강력하다.

🏗️ 기본 문법 - WITH CTE 구조

WITH cte_name AS (
  SELECT ...
)
-- INSERT / UPDATE / DELETE ...

하나의 CTE 뿐만 아니라 여러 개도 사용할 수 있다.

WITH cte1 AS (...),
     cte2 AS (...)

이제 각각의 DML에 어떻게 활용하는지 자세히 살펴보자.

반응형

📥 CTE를 활용한 INSERT 예제

어떤 조건에 맞는 데이터를 선별한 후, 결과를 다른 테이블에 삽입하고 싶을 때 유용하다.

WITH recent_users AS (
  SELECT id, name, created_at
  FROM users
  WHERE created_at > DATE_SUB(NOW(), INTERVAL 7 DAY)
)
INSERT INTO new_users_log (user_id, user_name, joined_at)
SELECT id, name, created_at
FROM recent_users;

✔️ 장점: 중복 SELECT 제거, 가독성 향상, 재활용 가능

🛠️ CTE를 활용한 UPDATE 예제

CTE를 통해 먼저 업데이트 대상과 조건을 미리 계산한 후, 메인 UPDATE에 활용하는 방식이다.

WITH to_update AS (
  SELECT id, status
  FROM orders
  WHERE shipped_at IS NOT NULL
)
UPDATE orders o
JOIN to_update u ON o.id = u.id
SET o.status = 'shipped';

✔️ 장점: 복잡한 조건 연산을 미리 분리하여 로직을 정리할 수 있다.

🗑️ CTE를 활용한 DELETE 예제

서브쿼리를 사용하지 않고도 CTE를 통해 명확하게 삭제 대상을 지정할 수 있다.

WITH old_logs AS (
  SELECT id
  FROM log_table
  WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR)
)
DELETE FROM log_table
WHERE id IN (SELECT id FROM old_logs);

✔️ 장점: 가독성이 뛰어나고, 재사용 가능한 로직을 분리할 수 있다.

반응형

⚠️ 주의할 점

• MySQL 8.0 이상에서만 사용 가능하다.
• CTE 내부에서는 LIMIT, ORDER BY 등을 자유롭게 사용할 수 있으나, INSERT/UPDATE/DELETE 대상 테이블과 JOIN 시에는 주의가 필요하다.
• 트랜잭션 내에서 사용하는 경우, ROLLBACK 테스트 필수이다.

📌 활용 팁

• 여러 단계의 JOIN을 수행해야 할 때 CTE를 나눠 사용하면 쿼리 튜닝에 효과적이다.
• 복잡한 로직을 나눠 설명하고 디버깅할 때도 큰 도움이 된다.
• MERGE가 없더라도 CTE + INSERT/UPDATE로 충분히 유사한 기능을 구현할 수 있다.

📎 참고 공식 문서

MySQL 공식문서: https://dev.mysql.com/doc/refman/8.0/en/with.html