INSERT·UPDATE·DELETE 고급 2

INSERT·UPDATE·DELETE 고급에서 INSERT의 확장 문법을 봤다. 이 문서는 DML(Data Manipulation Language, 데이터 조작어) 가운데 UPDATE·DELETE 쪽을 다룬다. 지울 행 수를 LIMIT으로 제한해 나눠 지우는 법, 다른 테이블과 조인해 고치거나 지우는 법, 여러 행을 서로 다른 값으로 한 문장에 고치는 법이다. 한 번에 너무 많이 바꾸면 잠금 대기가 길어지고, 문법을 잘못 고르면 엉뚱한 행이 바뀐다.

UPDATE·DELETE에 ORDER BY와 LIMIT

단일 테이블 UPDATE·DELETE에는 ORDER BY와 LIMIT을 붙일 수 있다.

-- 오래된 로그를 1,000행씩 나눠 지우기 (지운 행이 0이 될 때까지 반복)
DELETE FROM logs WHERE created_at < '2026-01-01' ORDER BY id LIMIT 1000;

-- id를 1씩 밀 때 큰 값부터 고쳐 중복 키 에러를 피하기
UPDATE seats SET id = id + 1 ORDER BY id DESC;

한 번에 수백만 행을 지우면 잠그는 행이 많아지고 트랜잭션(transaction)이 길어져, 그동안 같은 행을 고치려는 다른 트랜잭션이 기다려야 한다. 매뉴얼도 큰 삭제는 LIMIT으로 나눠 반복하라고 권장한다. 이때 ORDER BY를 함께 써야 매번 어느 행이 지워질지 정해진다. 위 UPDATE도 같은 원리다. id가 1, 2, 3일 때 1부터 고치면 2로 바꾸는 순간 아직 안 바뀐 2와 겹쳐 중복 키 에러가 나지만, 큰 값부터 옮기면 늘 빈 자리로만 옮긴다. 지울 행이 남길 행보다 훨씬 많다면, 남길 행만 새 테이블에 복사하고, RENAME TABLE로 두 테이블의 이름을 맞바꿔 새 테이블이 원래 이름을 갖게 한 뒤 옛 테이블을 지우는 방법도 있다.

UPDATE가 돌려주는 영향받은 행 수(affected rows)는 실제로 값이 바뀐 행 수다. 조건에 맞았어도 이미 같은 값이면 세지 않는다. "0행이 바뀌었다"가 "조건에 맞는 행이 없다"는 뜻은 아니다. 또 단일 테이블 UPDATE의 SET은 왼쪽부터 차례로 적용되므로 SET a = a + 1, b = a에서 b는 바뀐 뒤의 a를 받는다.

조인 UPDATE·DELETE

다른 테이블 값을 기준으로 고치거나 지울 때는 여러 테이블 문법을 쓴다.

UPDATE orders o JOIN users u ON u.id = o.user_id
SET o.grade = u.grade
WHERE u.updated_at >= '2026-09-01';

DELETE o FROM orders o JOIN users u ON u.id = o.user_id
WHERE u.status = 'WITHDRAWN';   -- orders 행만 지운다

여러 테이블 DELETE는 DELETE 바로 뒤에 적은 테이블의 행만 지우고, 나머지는 조건을 위해서만 읽는다. 별칭(alias, 예시의 o·u처럼 테이블에 붙인 짧은 이름)은 FROM 쪽에서 정의하고 앞에서는 별칭만 쓴다. 여러 테이블 UPDATE·DELETE에는 ORDER BY·LIMIT을 쓸 수 없고, UPDATE의 SET 적용 순서도 보장되지 않는다.

고치는 테이블을 서브쿼리(subquery, 문장 안에 괄호로 넣은 SELECT)에서 읽으면 "You can't specify target table ... for update in FROM clause"라는 에러 1093이 난다. 흔한 우회는 서브쿼리를 파생 테이블(derived table)로 한 번 더 감싸는 것이다. 파생 테이블 결과가 임시 테이블(temporary table)에 먼저 만들어지면 그 사본을 기준으로 원래 테이블을 고치므로, 같은 테이블을 읽으면서 고치는 상황이 사라진다. 그런데 옵티마이저(optimizer)가 파생 테이블을 바깥 쿼리에 병합(merge, 서브쿼리를 풀어 바깥 문장에 합치는 것)하면 다시 원래 테이블을 직접 읽게 되어 같은 에러가 난다. 그래서 매뉴얼 예시처럼 NO_MERGE 옵티마이저 힌트(optimizer hint)로 병합을 막는다.

UPDATE /*+ NO_MERGE(d) */ items,
       (SELECT id FROM items WHERE stock = 0) AS d
SET items.visible = 0
WHERE items.id = d.id;

여러 행을 서로 다른 값으로

행마다 다른 값으로 고칠 때 문장을 행 수만큼 보내면 애플리케이션과 DB 서버가 요청과 응답을 주고받는 네트워크 왕복(round trip)이 행 수만큼 늘어난다. 한 문장으로 묶는 방법은 두 가지다. SET price = CASE id WHEN 1 THEN 100 WHEN 2 THEN 200 END WHERE id IN (1, 2)처럼 CASE 식으로 행별 값을 주거나, 여러 행 INSERT ... ON DUPLICATE KEY UPDATE로 보낸다. 후자는 없는 키면 새 행이 생긴다는 점을 알고 쓴다. 어느 쪽이든 한 문장이 너무 커지지 않게 나눠 보낸다.

면접 포인트

• "대량 삭제는?" → ORDER BY ... LIMIT으로 나눠 반복, 또는 남길 행을 복사하고 RENAME TABLE.
• "UPDATE가 0행이라는데 행은 있어요" → 영향받은 행 수는 실제로 값이 바뀐 행 수다. 이미 같은 값이면 세지 않는다.
• "조인 DELETE는 무엇을 지우나?" → DELETE 바로 뒤에 적은 테이블의 행만. 여러 테이블 문법에는 ORDER BY·LIMIT을 못 쓴다.
• "에러 1093" → 고치는 테이블을 서브쿼리에서 읽었다. 파생 테이블 + NO_MERGE 또는 조인 UPDATE로 바꾼다.