백엔드 데이터 인프라 13편. UPDATE 데이터 수정의 표준 패턴 — SET·FROM·RETURNING·CASE 표현식·WHERE 안전 가드 풀어쓴 학습 노트.
이 글은 백엔드 데이터 인프라 시리즈 70편 중 13편이에요. 11편 SELECT ·12편 JOIN 까지 "조회" 를 다뤘으니, 이번 13편은 데이터 수정 — UPDATE.
UPDATE 기본 형태
UPDATE 테이블
SET 컬럼1 = 값1, 컬럼2 = 값2, ...
WHERE 조건
RETURNING *;
세 가지 절로 끊어 보면 SET은 무엇을 어떻게 바꿀지, WHERE는 어느 행을 바꿀지 (절대 빠뜨리면 안 돼요), RETURNING은 수정된 행을 결과로 돌려받는 절이에요. RETURNING은 PG만의 특별한 기능.
단건·여러 컬럼
-- 한 컬럼
UPDATE users SET name = 'Alice Smith' WHERE id = 1;
-- 여러 컬럼
UPDATE users
SET name = 'Alice Smith',
email = 'alice.smith@example.com',
updated_at = NOW()
WHERE id = 1;
여러 컬럼을 동시에 바꿔도 한 트랜잭션 안에서 묶여 처리돼요. INSERT 보다 자연스러운 묶음이죠.
표현식 — 단순 값이 아닌
-- 현재 값에 더하기
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';
-- 다른 컬럼 참조
UPDATE orders SET total = amount + shipping_fee;
-- NULL 처리
UPDATE users SET email = COALESCE(email, 'no-email@example.com');
SET col = 표현식 자리에는 어떤 표현식이든 박을 수 있어요. COALESCE(NULL이면 대체값 반환)처럼 함수도 자연스럽게 들어가요.
CASE — 조건부 수정
UPDATE products
SET status = CASE
WHEN stock = 0 THEN 'SOLDOUT'
WHEN stock < 10 THEN 'LOW_STOCK'
ELSE 'IN_STOCK'
END;
한 SQL 안에서 여러 케이스를 분기 처리할 수 있어 대량 일괄 수정의 표준 패턴이에요.
FROM — 다른 테이블 값으로 (PG 강점)
UPDATE users u
SET last_login = a.last_at
FROM activities a
WHERE u.id = a.user_id;
UPDATE ... FROM 은 MySQL에는 없는 PG만의 강력한 기능이에요. "JOIN해서 다른 테이블 값으로 갱신" 하고 싶을 때 쓰는 절.
서브쿼리로
UPDATE users
SET order_count = (
SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id
);
이 방식은 각 행마다 서브쿼리를 실행해서 큰 테이블에서는 느려져요. FROM 절 형태가 보통 더 빨라요.
RETURNING — 수정된 행 받기
UPDATE users
SET name = 'Alice Smith'
WHERE id = 1
RETURNING id, name, updated_at;
수정이 끝나자마자 "실제로 무엇이 바뀌었나" 를 바로 확인할 수 있어 운영 환경에서 정말 유용해요.
조건부 RETURNING
UPDATE products
SET price = price * 1.1
WHERE category = 'electronics'
RETURNING id, name, price AS new_price;
수정된 행과 새 값을 함께 명시해서 돌려받는 형태.
WHERE 없는 UPDATE — 무서운 함정
UPDATE users SET name = 'Test'; -- ❌ 모든 행 수정
운영 환경에서 가장 무서운 사고가 바로 이거예요. 한 번 실행되는 순간 모든 사용자 이름이 'Test'로 바뀌어 버려요. 그래서 PG 안전 가드 패턴 이 필요해요.
BEGIN;
-- 1. SELECT로 영향 행 확인
SELECT COUNT(*) FROM users WHERE created_at < '2020-01-01';
-- 100 출력 — OK
-- 2. UPDATE
UPDATE users SET status = 'INACTIVE' WHERE created_at < '2020-01-01';
-- UPDATE 100 — 예상치와 일치
-- 3. 검증
SELECT COUNT(*) FROM users WHERE status = 'INACTIVE';
-- 4. 의도와 같으면 COMMIT, 다르면 ROLLBACK
COMMIT;
-- 또는 ROLLBACK;
이 5단계 흐름이 운영 안전 표준으로 자리잡았어요.
운영 안전 추가 기법
sql_safe_updates (옵션)
SET session_user_safe_updates = on; -- 세션 수준 안전 모드
WHERE 없는 UPDATE·DELETE 를 거부하는 모드인데 PG에 직접 기능은 없어요. 대신 클라이언트(DBeaver 등)의 안전 모드를 켜거나 트리거로 구현하는 식.
LIMIT — 안전 제한
-- PG 에 표준 UPDATE LIMIT 없음. 단 — CTE로 우회 가능
WITH targets AS (
SELECT id FROM users WHERE created_at < '2020-01-01' LIMIT 100
)
UPDATE users SET status = 'INACTIVE'
WHERE id IN (SELECT id FROM targets);
대량 UPDATE를 작은 단위로 chunk 처리하면 락 시간이 짧아지고 트랜잭션 부담도 줄어들어요.
UPDATE 와 트랜잭션·MVCC
자바 백엔드 입문 43편 @Transactional 의 트랜잭션이 UPDATE의 안전망 역할을 해요. PG는 38편 MVCC 로 "내 UPDATE가 다른 트랜잭션의 SELECT를 막지 않음" 을 보장하고요.
-- 트랜잭션 A
BEGIN;
UPDATE users SET name = 'New' WHERE id = 1;
-- 아직 COMMIT 안 함
-- 트랜잭션 B (다른 세션)
SELECT name FROM users WHERE id = 1;
-- 'Old' 보임 — 아직 A의 변경이 B에 안 보임 (MVCC)
Spring JPA 의 UPDATE
자바 백엔드 입문 47편 영속성 컨텍스트 의 변경 감지(Dirty Checking) 가 UPDATE를 자동으로 만들어 줘요.
@Transactional
public void updateName(Long id, String newName) {
User user = userRepository.findById(id).orElseThrow();
user.setName(newName);
// 트랜잭션 종료 시 Hibernate가 자동 감지 → UPDATE
}
→ 자동 생성 SQL:
UPDATE users SET name = ?, updated_at = ? WHERE id = ?;
JPA의 마법처럼 보이지만 "실제 어떤 UPDATE가 박히나" 모르면 디버깅이 안 돼요.
Bulk UPDATE — JPQL
@Modifying
@Query("UPDATE User u SET u.status = 'INACTIVE' WHERE u.lastLoginAt < :date")
int markInactiveOldUsers(@Param("date") LocalDateTime date);
큰 데이터를 일괄 변경할 때는 JPQL의 UPDATE 를 써요. 1차 캐시 무효화는 따로 신경 써야 하는 부분.
운영 시나리오 — 5가지
(1) 단일 행 수정 (가장 흔함)
UPDATE users SET email = 'new@example.com' WHERE id = 1;
(2) 상태 변경
UPDATE orders SET status = 'SHIPPED' WHERE id = 100 AND status = 'PAID';
조건에 "현재 상태" 도 함께 박아서 잘못된 전이를 차단하는 패턴이에요.
(3) 일괄 갱신 (chunk)
WITH chunk AS (
SELECT id FROM users WHERE status = 'PENDING' LIMIT 1000 FOR UPDATE
)
UPDATE users SET status = 'ACTIVE' WHERE id IN (SELECT id FROM chunk);
(4) 통계 갱신
UPDATE products p
SET order_count = (SELECT COUNT(*) FROM orders WHERE product_id = p.id);
(5) 마이그레이션
UPDATE users SET name = TRIM(name); -- 공백 정리
함정 5가지
(1) WHERE 누락 — 모든 행 수정
가장 무서운 사고예요. BEGIN 으로 열고 SELECT COUNT 로 검증한 다음 UPDATE → COMMIT 순으로 가야 안전해요.
(2) UPDATE 한꺼번에 1억 건
락·WAL(Write-Ahead Log·변경 로그)·메모리가 한꺼번에 폭발해요. chunk 1000~10000건 단위로 끊는 게 표준.
(3) updated_at 자동 갱신 잊음
UPDATE users SET name = 'New' WHERE id = 1;
-- updated_at 안 박힘 — 디버깅 불가
표준은 항상 함께 박는 것, 아니면 트리거로 처리해요.
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
이 트리거 한 번 박아두면 UPDATE 마다 자동으로 갱신돼요.
(4) JPA Dirty Checking 의 환상
User user = userRepository.findById(id).get();
user.setName("New");
// → UPDATE 자동 발행되지만, 트랜잭션 안에서만
트랜잭션 밖에서 setName을 호출하면 UPDATE가 안 박혀요. @Transactional 이 필수.
(5) 잘못된 컬럼명·오타
PG가 에러를 출력하긴 해도 큰 SQL 안에 묻혀서 못 잡을 수 있어요. 운영에서는 SQL을 별도로 검토하고 EXPLAIN 한 번 돌려보는 게 안전해요.
(1) BEGIN, (2) SELECT COUNT 검증, (3) UPDATE 실행 + 행수 확인, (4) RETURNING으로 결과 검증, (5) COMMIT 또는 ROLLBACK. 이 5단계가 운영 사고를 막아요.
한 줄 정리 — UPDATE SET·WHERE·RETURNING 3절 + FROM·CASE 표현식. WHERE 누락이 가장 무서운 함정. 운영 = BEGIN-SELECT-UPDATE-검증-COMMIT 5단계. JPA는 Dirty Checking, Bulk는 JPQL @Modifying. updated_at 트리거가 표준.
시험 직전 한 번 더 — UPDATE 입문자가 매번 헷갈리는 것
UPDATE t SET col=val WHERE 조건- 여러 컬럼 = 한 SET 안에 콤마
- 표현식 SET =
price = price * 1.1 - CASE 표현식 = 조건부 수정
- FROM = 다른 테이블 값으로 (PG 강점)
- RETURNING = 수정된 행 결과
- WHERE 누락 = 모든 행 (가장 무서운 함정)
- 운영 5단계 = BEGIN → SELECT → UPDATE → 검증 → COMMIT/ROLLBACK
- chunk UPDATE = CTE LIMIT + IN
FOR UPDATE= 행 락- updated_at 자동 갱신 = 트리거
- JPA Dirty Checking = 트랜잭션 안에서만 작동
- @Modifying @Query = JPA Bulk UPDATE
- MVCC = 내 UPDATE가 다른 SELECT 안 막음
- 잘못된 상태 전이 차단 = WHERE에 현재 상태 함께
- 큰 UPDATE = chunk 1000~10000건
- COALESCE·NULLIF = NULL 처리
- 상태 컬럼 = ENUM 또는 CHECK
- COUNT 검증 = 영향 행 사전 확인
- ROLLBACK = 결과 의심스러우면
- JPQL UPDATE = 1차 캐시 무효화 주의
- 한국 백엔드 = 단일 행 UPDATE 가 99%
시리즈 다른 편 (앞뒤 글 모음)
이전 글:
- 8편 — SQL 기초 5가지 동사
- 9편 — CREATE TABLE 첫 테이블 만들기
- 10편 — INSERT 데이터 입력 표준 패턴
- 11편 — SELECT 데이터 조회 표준 패턴
- 12편 — JOIN 여러 테이블 합치기
다음 글: