[DBMS] 데이터의 변경을 위한 SQL문 - UPDATE, DELETE, MERGE

Nov 30, 2024

[DBMS] 데이터의 변경을 위한 SQL문 - UPDATE, DELETE, MERGE

Contents

1. 데이터 수정 UPDATE 2. 데이터 삭제 DELETE 3. 대용량 데이터 삭제 4. 조건부 데이터 변경 MERGE

1. 데이터 수정 UPDATE

데이터를 변경하기 위한 UPDATE 문의 문법은 다음과 같다.


UPDATE 테이블명
SET 컬럼1 = 값1, 컬럼2 = 값2 ...
WHERE 조건 ;

WHERE 절에 조건을 넣지 않으면 테이블 전체에 적용되므로 꼭 WHERE 절을 사용해야 한다.


USE tempdb

UPDATE testTbl6
SET Lname = 'update-test'
WHERE Lname = 'Abel'


UPDATE testTbl6
SET Fname = 'update-test2'

WHERE 절 조건을 넣지 않으면 모든 행에 영향을 받게 되며, 다시 되돌릴 수 없는 경우도 있으니 주의해야 한다.

2. 데이터 삭제 DELETE

DELETE도 UPDATE와 비슷한 개념이다.


DELETE 테이블명 WHERE 조건;

UPDATE와 동일하게 WHERE 절이 생략되면 테이블의 전체 테이블이 삭제되니 주의해야 한다.


DELETE testTbl6
WHERE Lname = 'Adams'

실행하면 Lname 값이 ‘Adams’인 86개의 행이 삭제되었다.

3. 대용량 데이터 삭제

만약 대용량의 테이블이 더 이상 필요없다면 어떻게 삭제하는 것이 좋을까?

테스트를 위해 대용량의 테이블 3개를 생성해보자.


USE tempDB

SELECT  * INTO bigTbl1 FROM AdventureWorks2022.Sales.SalesOrderDetail
SELECT  * INTO bigTbl2 FROM AdventureWorks2022.Sales.SalesOrderDetail
SELECT  * INTO bigTbl3 FROM AdventureWorks2022.Sales.SalesOrderDetail

약 12만 건의 데이터가 있는 AdventureWorks2022 데이터베이스의 SalesOrderDetail 테이블을 복사한다.

도구 > SQL Server Profiler 를 선택해 프로파일러를 실행한다.

프로파일러 이름을 넣은 후 탬플릿은 TSQL_DURATION 을 선택한다.


DELETE FROM bigTbl1
GO
DROP TABLE  bigTbl2
GO
TRUNCATE TABLE bigTbl3

그리고 3개의 DELETE, DROP, TRUNCATE 문으로 테이블을 삭제한다.

💡

DELETE, DROP, TRUNCATE 는 모두 데이터를 삭제하는 명령어이지만 쓰임은 조금 다르다. 1. DELETE - 조건부 데이터 삭제를 위해 사용

TRUNCATE - 테이블의 모든 데이터를 삭제하기 위해 사용

DROP - 테이블 자체를 삭제

프로파일러 결과를 보면 DROP 이나 TRUNCATE에 비해 DELETE가 더 오래 걸린 것을 알 수 있다.

이는 DML 문인 DELETE는 트랜잭션 로그를 기록하는 작업 때문에 삭제가 오래 걸린다.

반면 DDL 문인 DROP , TRUNCATE 는 트랜잭션 로그를 기록하지 않아 속도가 빠르다.

그러므로 대용량의 테이블 전체를 사제할 때는 DELETE 보다 TRUNCATE, 테이블 자체가 필요없다면 DROP을 사용하는 것이 효율적이다.

4. 조건부 데이터 변경 MERGE

MERGE문의 문법은 아래와 같다.


MERGE INTO target_table AS target
USING source_table AS source
ON (join_condition)
WHEN MATCHED THEN
    UPDATE SET
        target.column1 = source.column1,
        target.column2 = source.column2
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...)
    VALUES (source.column1, source.column2, ...)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

쉽게 얘기하면 데이터를 가져올 Source 테이블과 데이터를 입력할 Target 테이블의 값을 비교해서,

일치하는 값(WHEN MATCHED THEN)은 업데이트, 일치하지 않는 값(WHEN NOT MATCHED THEN) 은 INSERT 나 DELETE를 하는 문법이다.

이해를 위해 예제를 연습하본다.

예제의 시나리오는 다음과 같다.

💡

멤버 테이블(emeberTb1) 에는 기존 회원들이 있는데, 이 테이블에 직접 INSERT, DELETE, UPDATE를 사용하면 안된다. 그리고 회원의 가입, 변경, 탈퇴가 생기면 변경 테이블(changeTB1) 에 INSERT 문으로 회원 변경 사항을 입력한다.

변경 테이블의 작성이 완료된 후, MERGE문으로 변경 테이블의 내용이 ‘신규가입’이면 멤버 테이블에 새로운 회원을 등록하고, ‘주소 변경’ 이면 멤버 테이블의 주소를 변경하고, ‘회원 탈퇴’면 멤버 테이블에서 해당 회원을 삭제하는 SQL을 만들어보자.


USE sqlDB

SELECT userID, name, addr INTO memberTb1 FROM userTb1

우선 기존 userTb1을 활용해 멤버 테이블을 정의한다.


CREATE TABLE changeTb1 (
	changeType NCHAR(4), -- 변경사유
	userID CHAR(8),
	name NVARCHAR(10),
	addr NCHAR(2)
)

INSERT INTO changeTb1 VALUES
('신규가입','TPN','티파니','미국'),
('주소변경','LSG',NULL,'제주'),
('주소변경','LJB',NULL,'영국'),
('회원탈퇴','BBK',NULL,NULL),
('회원탈퇴','SSK',NULL,NULL)

그리고 변경 테이블을 생성 후 데이터를 입력한다.

그리고 변경사유(changeType)열에 의해서 기존 멤버 테이블의 데이터를 변경한다.


MERGE memberTB1 AS m  -- 변경될 target 테이블
	USING changeTb1 AS c -- 변경할 기준 테이블 
	ON m.userID = c.userID -- 두 테이블의 userID를 기준으로 비교
		
	WHEN NOT MATCHED and changeType = '신규가입' THEN
		INSERT (userID,name,addr) VALUES (c.userID,c.name,c.addr)

	WHEN MATCHED and changeType = '주소변경' THEN
		UPDATE SET m.addr = c.addr

	WHEN MATCHED and changeType = '회원탈퇴' THEN
		DELETE ; 


SELECT * FROM memberTb1