데이터베이스 UPDATE DML문 사용법 및 예제

UPDATE DML문 테이블에 입력된 자료를 수정하기 위한 DML 문으로 보통 database TABLE의 record의 column 값을 수정할 때 조건을 넣어서 UPDATE 문을 사용합니다.

UPDATE DML기본 구조

UPDATE DML 문 기본 Syntax.

  MSSQL update 문 기본 Syntax.
   UPDATE [TABLE 명]
      SET  [column1] = [value1], [column2] = [value2], ...
    WHERE [조건문];

기본적인 UPDATE 방법.

• 질문, UserInfoForUPDATE 테이블 내용 중 UserAge column을 모두 100으로 변경해 주세요.

-- 기본적인 UPDATE 방법
UPDATE UserInfoForUpdate
   SET  UserAge = '100'

여러 열 업데이트할 수 있는 UPDATE DML.

• 질문, UserId가 202405 회원은 UserNm을 공백으로, UserAge를 0으로 변경해 보세요.

--  여러 열 업데이트
UPDATE UserInfoForUpdate
   SET UserNm = ''
      ,UserAge = '100'

TOP 절 사용 할 수 있는 UPDATE DML.

• 질문, UserAge data 중 50보다 적은 값 중 2건만 50으로 변경하세요.
• 100만 건을 한 번에 변경하기보다는 1000건씩 나눠서 변경하는 경우 사용합니다.

-- TOP 절 사용
UPDATE TOP (2) UserInfoForUpdate
   SET  UserAge = '50'
   where UserAge < 50;

UPDATE DML문 심화 활용 예시

WITH common_table_expression 절 사용.

• 질문, WITH 절을 이용하여 update 문을 작성해 보세요.
• with절은 재귀 query에서도 사용하지만 update문에서도 사용가능합니다.

	-- WITH common_table_expression 절 사용
WITH cte(UserId, UserNm,  UserAge) AS  
(  
	SELECT a.UserId
          ,a.UserNm
          ,a.UserAge  
	FROM UserInfoForUpdate AS a 
	WHERE a.UserId like '202404' 
)  
UPDATE BoardJoinUpdate  
   SET UserNm = c.UserNm  
FROM BoardJoinUpdate AS c  
JOIN cte AS d 
  ON c.UserId = d.UserId  
WHERE d.UserNm <>  '';

WHERE CURRENT OF 절 사용.

• WHERE CURRENT OF 절을 사용하여 커서가 있는 행만 업데이트합니다.
• 질문, WHERE CURRENT OF 절을 이용하여 User Age를 90으로 변경해 보세요.
• CURSOR 부분을 활용하여 UPDATE DML문을 사용하는 예제입니다.

-- WHERE CURRENT OF 절 사용
DECLARE  fech_cursor CURSOR FOR  
	SELECT a.UserId          
	FROM UserInfoForUpdate AS a 
	WHERE a.UserId = '202404' 
	;  
OPEN fech_cursor;  

FETCH FROM fech_cursor;  
UPDATE UserInfoForUpdate 
SET UserAge = 90   

WHERE CURRENT OF fech_cursor;  
CLOSE fech_cursor;  
DEALLOCATE fech_cursor;

SET 절에 하위 질의문 및 join 절 사용.

• 질문, set 절에 하위 질의문을 사용하여 BoardJoinUpdate의 카운트만큼 100을 더해주세요.
• subquery 이해해야 합니다.
• 현업에서는 단순 UPDATE DML문보다는 join을 활용하여 사용합니다.

-- SET 절에 하위 쿼리 지정
UPDATE UserInfoForUpdate  
SET UserAge = 100  +  (SELECT count(*)  cnt 
                        FROM BoardJoinUpdate AS BU  
                       WHERE UserInfoForUpdate.UserId  = bu.UserId
                      );

-- 다른 테이블의 정보와 함께 UPDATE 문 사용 
UPDATE BoardJoinUpdate  
	SET UserNm = c.UserNm  
FROM BoardJoinUpdate AS c  
JOIN UserInfoForUpdate AS d 
	ON c.UserId = d.UserId  
WHERE d.UserNm <>  '';

OUTPUT을 이용하여 UPDATE DML 문 사용.

• 질문, OUTPUT을 이용하여 UPDATE 전, 후 값을 확인해 주세요.
• update문은 매우 위험한 작업으로 전, 후 query 검증을 위해서 사용됩니다.
• 테이블 변수를 활용하세요.

-- 테이블 변수 선언
DECLARE @UserInfoForUpdateVar table (  
	UserId int NOT NULL,
	oldUserNm varchar(255),
	newUserNm varchar(255),
	oldUserAge  int,
	newUserAge  int
);  
 
-- 삭제하면서 테이블 변수에 넣기.
UPDATE UserInfoForUpdate
	SET UserNm = 'changeName' + UserNm
		,UserAge = '30'
OUTPUT inserted.UserId,  
       deleted.UserNm,
       inserted.UserNm,
       deleted.UserAge,
       inserted.UserAge  
  INTO @UserInfoForUpdateVar
FROM UserInfoForUpdate     
;  
  
--삭제 데이터 확인 
SELECT * 
FROM @UserInfoForUpdateVar;

TRY…CATCH 블록에 UPDATE 사용.

• 질문 TRY CATHCH와 TRANSACTION 문을 이용하여 에러가 없을 때만 UPDATE DATA를 적용해 주세요.
• 소스를 통해서 error 함수를 이해하면 좋습니다.
• transation의 commit, rollback을 변경해서 실습해 보세요.

-- TRY…CATCH 블록에 UPDATE 사용
BEGIN TRANSACTION;  
  
BEGIN TRY   
	UPDATE UserInfoForUpdate
	SET UserNm = N'MyNewTryCatchName'  
	WHERE UserAge BETWEEN 30 AND 100;  
END TRY  
BEGIN CATCH  
	SELECT ERROR_NUMBER() AS ErrorNumber  
          ,ERROR_SEVERITY() AS ErrorSeverity  
          ,ERROR_STATE() AS ErrorState  
          ,ERROR_PROCEDURE() AS ErrorProcedure  
          ,ERROR_LINE() AS ErrorLine  
          ,ERROR_MESSAGE() AS ErrorMessage;  
  
	IF @@TRANCOUNT > 0  
		ROLLBACK TRANSACTION;  
END CATCH;  
  
IF @@TRANCOUNT > 0  
	COMMIT TRANSACTION;  
GO

UPDATE DML문 주의 사항

WHERE 절을 정확하게 지정.

UPDATE DML 문은 테이블의 모든 행을 수정할 수 있으므로 WHERE 절을 정확하게 사용하여 원하는 DATA만 업데이트하도록 하세요.

UPDATE DML 트랜잭션을 사용.

데이터를 수정하는 작업은 중요하므로, 잘못된 업데이트는 데이터 무결성을 훼손할 수 있습니다.
그러므로 TRY CATHCH와 트랜잭션을 사용하여 업데이트 작업을 안전하게 작업하세요.

백업을 수행.

데이터를 수정하기 전에 관련 DATA를 백업해 주세요. 예상치 못한 문제가 발생하면 백업 데이터를 통해서 복원해야 합니다.

변경된 데이터를 확인.

업데이트 작업을 수행하면서 전, 후 값을 확인하여 예상대로 업데이트되었는지 확인하세요.

테이블 생성 소스입니다.

USE sampleDB;
DROP TABLE IF EXISTS UserInfoForUpdate;
CREATE TABLE UserInfoForUpdate (
    UserId int NOT NULL,
    UserNm varchar(255),
    UserAge  int
);

insert into  UserInfoForUpdate(UserId,UserNm,UserAge) 
values
('202401','kim',60),
('202402','lee',84),
('202403','park',90),
('202404','moon',65),
('202405','young',35),
('202406','cho',45)


USE sampleDB;
DROP TABLE IF EXISTS BoardJoinUpdate;
CREATE TABLE BoardJoinUpdate (
    BoardNo int NOT NULL,
    BoardTitle varchar(255),
    UserId  int,
	UserNm  varchar(255),
);
 
insert into  BoardJoinUpdate(BoardNo,BoardTitle,UserId) 
values
(1,'seoul',202401),
(2,'busun',202402), 
(3,'inchen',202404);