SQL INSERT UPDATE DELETE 사용법 - 안전하게 데이터 변경하기 (SQL 초급 5단원)

이 단원에서 배우는 것

4단원에서 타입과 제약조건으로 방어선을 쳤다. 이제 그 방어선 안으로 실제 데이터를 넣고, 고치고, 지운다. 조회는 잘못 써도 결과만 이상하지만 UPDATE 와 DELETE 는 한 번 잘못 실행하면 데이터가 사라진다. 그래서 이 단원은 문법보다 실행하기 전에 무엇을 확인하는가에 무게를 둔다. 2단원에서 배운 WHERE 가 여기서는 안전장치 역할을 한다.

• INSERT·UPDATE·DELETE 의 기본형과 INSERT ... SELECT 를 쓴다.
• 트랜잭션으로 감싸 실수를 되돌리고, 영향 행 수로 검증한다.
• WHERE 없는 UPDATE 를 구조적으로 막는 습관 세 가지를 몸에 붙인다.

개념

운영 DB 에서 데이터를 지우는 사고는 대개 문법을 몰라서 생기지 않는다. 문법은 다 안다. 사고는 세 가지 상황에서 난다. 첫째, WHERE 를 쓰긴 썼는데 조건이 생각보다 넓었다. 둘째, 편집기에서 문장 일부만 드래그해 실행하는 바람에 WHERE 앞까지만 서버로 갔다. 셋째, 개발 DB 인 줄 알고 실행했는데 운영 접속 창이었다.

이 셋에 공통으로 듣는 대책이 있다. 같은 WHERE 로 SELECT 를 먼저 돌려 대상 건수를 눈으로 본 다음, 트랜잭션 안에서 DML 을 실행하고, 영향 행 수가 예상과 같을 때만 COMMIT 하는 것이다. 시간은 30초 더 걸리고, 사고는 거의 없어진다.

표준 SQL 문법과 예제

INSERT

-- 컬럼 목록을 반드시 적는다
INSERT INTO dept (dept_id, dept_name, location)
VALUES (60, '보안팀', '서울');

-- 여러 행을 한 번에. 왕복 횟수가 줄어 훨씬 빠르다
INSERT INTO dept (dept_id, dept_name, location) VALUES
 (70, '법무팀', '서울'),
 (80, '홍보팀', '부산');

-- 조회 결과를 그대로 넣는다. 작업 전 백업을 뜰 때 자주 쓴다
CREATE TABLE emp_backup (
    emp_id   INT           NOT NULL PRIMARY KEY,
    emp_name VARCHAR(30)   NOT NULL,
    salary   DECIMAL(10,2) NOT NULL
);

INSERT INTO emp_backup (emp_id, emp_name, salary)
SELECT emp_id, emp_name, salary FROM emp WHERE dept_id = 20;
-- Query OK, 3 rows affected (0.01 sec)
-- Records: 3  Duplicates: 0  Warnings: 0

컬럼 목록을 생략한 INSERT INTO dept VALUES (60, '보안팀', '서울') 도 문법상 된다. 하지만 나중에 컬럼이 하나 추가되는 순간 이 문장은 전부 깨진다. 목록을 적는 데 드는 몇 초가 그 사고를 막는다.

UPDATE

-- 1단계: 같은 WHERE 로 대상을 먼저 본다
SELECT emp_id, emp_name, salary
FROM   emp
WHERE  dept_id = 20 AND job = '개발자';

-- 2단계: 대상이 맞으면 트랜잭션 안에서 실행한다
START TRANSACTION;

UPDATE emp
SET    salary = salary * 1.05
WHERE  dept_id = 20 AND job = '개발자';
-- Query OK, 2 rows affected (0.00 sec)
-- Rows matched: 2  Changed: 2  Warnings: 0

-- 3단계: 예상한 2건이 맞으면 확정, 아니면 되돌린다
COMMIT;
-- ROLLBACK;

Rows matched 는 WHERE 에 걸린 행 수, Changed 는 값이 실제로 바뀐 행 수다. 이미 같은 값이면 matched 에는 잡히고 changed 에는 안 잡힌다. 두 숫자가 다르면 조건이나 데이터를 다시 확인해 볼 이유가 있다.

DELETE 와 TRUNCATE

-- 조건에 맞는 행만 삭제. 되돌릴 수 있고 트리거도 동작한다
DELETE FROM dept WHERE dept_id IN (60, 70, 80);

-- 테이블을 통째로 비운다. DDL 이라 대부분의 DB 에서 되돌릴 수 없다
TRUNCATE TABLE emp_backup;

TRUNCATE 는 행을 하나씩 지우지 않고 저장 공간을 통째로 반납하므로 전체 삭제에서 압도적으로 빠르다. 대신 WHERE 를 못 쓰고, 자동 증가 값이 초기화되며, MySQL·Oracle 에서는 롤백이 안 된다. 테스트 데이터 초기화에는 쓰고, 운영 테이블에는 손대지 않는다.

DB별 차이

항목	MySQL / MariaDB	PostgreSQL	Oracle	SQL Server
기본 커밋 모드	autocommit ON	psql 은 문장 단위 자동 커밋	수동 커밋. COMMIT 을 쳐야 반영	autocommit ON
트랜잭션 시작	START TRANSACTION	BEGIN	DML 실행 시 자동 시작	BEGIN TRAN
TRUNCATE 롤백	불가	가능	불가	가능
UPSERT	ON DUPLICATE KEY UPDATE	ON CONFLICT DO UPDATE	MERGE	MERGE
변경된 행 돌려받기	MySQL 없음 / MariaDB 는 INSERT·DELETE·REPLACE 에 RETURNING(UPDATE 는 13.0 부터)	RETURNING	RETURNING INTO	OUTPUT
DDL 이 트랜잭션에 포함	아니오(암묵적 커밋)	예	아니오(암묵적 커밋)	예

MySQL 에서 ALTER TABLE 을 실행하면 그 시점에 진행 중이던 트랜잭션이 자동으로 커밋된다. "트랜잭션으로 감쌌으니 안전하다"고 생각하고 중간에 DDL 을 끼워 넣으면 앞의 DML 이 이미 확정되어 롤백되지 않는다.

실무에서 자주 틀리는 것

1. WHERE 없는 UPDATE / DELETE

가장 흔하고 가장 크게 터진다. 습관 세 가지로 막는다.

첫째, UPDATE 를 칠 때 WHERE 절부터 쓴다. UPDATE emp WHERE emp_id = 1001 을 먼저 적어 놓고 커서를 되돌려 SET 을 채운다. 이렇게 하면 작성 도중에 실행 단축키를 눌러도 문법 오류가 날 뿐 데이터는 안 바뀐다.

둘째, MySQL 이라면 안전 모드를 켠다. 인덱스 컬럼이 든 WHERE 나 LIMIT 이 없는 UPDATE·DELETE 를 서버가 거부한다.

-- 접속 후 세션마다
SET SQL_SAFE_UPDATES = 1;

DELETE FROM emp;
-- ERROR 1175 (HY000): You are using safe update mode and you tried to
-- update a table without a WHERE that uses a KEY column

-- 아예 클라이언트 실행 시 켜 두는 편이 낫다
-- mysql --safe-updates -u ops -p opsdb

셋째, 여러 문장을 한 창에 늘어놓고 드래그해서 실행하지 않는다. 부분 실행 사고는 전부 여기서 난다. 작업 SQL 은 파일로 만들어 통째로 실행한다.

2. NULL 에 산술 연산을 한다

-- 의도: 모든 사원의 보너스를 100 올린다
UPDATE emp SET bonus = bonus + 100;

보너스가 있던 사원만 오르고, NULL 이던 5명은 NULL + 100 = NULL 이라 그대로 NULL 이다. 오류도 안 나고 경고도 없다. NULL 을 0 으로 보고 계산하려면 명시적으로 바꿔 준다.

UPDATE emp SET bonus = COALESCE(bonus, 0) + 100;

COALESCE 는 표준 함수라 네 DB 에서 모두 동작한다. MySQL 의 IFNULL, Oracle 의 NVL 은 각자 전용이다.

3. 커밋을 안 하고 자리를 뜬다

Oracle 이나 명시적 트랜잭션을 연 상태에서 COMMIT 없이 창을 닫으면 작업이 통째로 롤백된다. 반대로 더 나쁜 경우도 있다. 커밋을 안 한 상태로 세션을 열어 두면 그 행들에 잠금이 걸려 있어서, 같은 행을 건드리는 다른 세션이 전부 대기에 빠진다. 서비스 지연 원인을 찾다 보면 누군가 열어 놓은 SQL 편집기가 범인인 경우가 실제로 많다. 트랜잭션은 짧게 열고 바로 닫는다.

4. 지우기 전에 백업을 안 뜬다

-- 삭제 대상만 별도 테이블로 떠 둔다. 날짜를 이름에 넣으면 나중에 정리하기 쉽다
CREATE TABLE emp_bak_20260825 AS
SELECT * FROM emp WHERE hire_date < DATE '2016-01-01';

DELETE FROM emp WHERE hire_date < DATE '2016-01-01';

Oracle 도 CREATE TABLE ... AS SELECT 를 그대로 쓴다. SQL Server 는 SELECT * INTO emp_bak_20260825 FROM emp WHERE ... 형태다. 백업 테이블은 인덱스와 제약조건을 복사하지 않으므로 되돌릴 때는 값만 다시 넣는 용도로 쓴다.

스스로 확인하기

1. 부서번호 90, 이름 '감사팀', 위치 '서울' 인 부서를 추가하라.
2. 영업팀(30) 사원 전원의 연봉을 3% 올려라. 실행 전 확인과 되돌릴 수단을 포함해서 작성하라.
3. 보너스가 없는 사원의 보너스를 0 으로 채워라. bonus = bonus + 0 으로는 왜 안 되는지 설명하라.

-- 1
INSERT INTO dept (dept_id, dept_name, location) VALUES (90, '감사팀', '서울');

-- 2
SELECT emp_id, emp_name, salary FROM emp WHERE dept_id = 30;   -- 3건 확인
START TRANSACTION;
UPDATE emp SET salary = salary * 1.03 WHERE dept_id = 30;
-- Rows matched: 3 이면 COMMIT, 아니면 ROLLBACK
COMMIT;

-- 3
UPDATE emp SET bonus = 0 WHERE bonus IS NULL;
-- bonus + 0 은 NULL + 0 이라 결과가 다시 NULL 이다.
-- NULL 이 섞인 연산의 결과는 언제나 NULL 이므로 값을 직접 지정해야 한다.