How to use the database UPDATE DML statement

We will explain the UPDATE statement used when modifying the column value of a record in a database TABLE.

Basic structure of UPDATE DML statement

Syntax of UPDATE DML statement.

   -- Syntax.
   UPDATE [TABLE NAME]
      SET  [column1] = [value1], [column2] = [value2], ...
    WHERE [condition];

How to use basic UPDATE statements.

• Question: Please change all UserAge columns to 100 among the contents of the UserInfoForUPDATE table.

-- Code
UPDATE UserInfoForUpdate
   SET  UserAge = '100'

UPDATE DML that allows multiple columns to be updated.

• Question: For UserId 202405 members, try changing UserNm to blank and UserAge to 0.

--  Code
UPDATE UserInfoForUpdate
   SET UserNm = ''
      ,UserAge = '100'

TOP clause can be used in UPDATE DML.

• Question: change only 2 of the values ??less than 50 in the UserAge data to 50.
• Used when changing 1,000 items at a time rather than changing 1 million items at once.

-- Use TOP clause
UPDATE TOP (2) UserInfoForUpdate
   SET  UserAge = '50'
   where UserAge < 50;

Example of advanced use of UPDATE DML statement

How to use the WITH common_table_expression clause.

• Question: Try writing an update statement using the WITH clause.
• The with clause can be used not only in recursive queries but also in update statements.

-- 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 <>  '';

How to use the WHERE CURRENT OF clause.

• Use the WHERE CURRENT OF clause to update only the row where the cursor is located.
• Question, try changing the User Age to 90 using the WHERE CURRENT OF clause.
• This is an example of using the UPDATE DML statement using the CURSOR part.

-- 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;

How to use subqueries and join clauses in the SET clause.

• Question: Add 100 to the count of BoardJoinUpdate using a subquery in the set clause.
• You must understand subqueries.
• In actual work, join is used rather than a simple UPDATE DML statement.

-- SET clauses
UPDATE UserInfoForUpdate  
SET UserAge = 100  +  (SELECT count(*)  cnt 
                        FROM BoardJoinUpdate AS BU  
                       WHERE UserInfoForUpdate.UserId  = bu.UserId
                      );

-- Using UPDATE statements with information from other tables
UPDATE BoardJoinUpdate  
   SET UserNm = c.UserNm  
FROM BoardJoinUpdate AS c  
JOIN UserInfoForUpdate AS d 
  ON c.UserId = d.UserId  
WHERE d.UserNm <>  '';

How to use the UPDATE DML statement using OUTPUT.

• Question: Please use OUTPUT to check the values ??before and after UPDATE.
• The update statement is a very dangerous operation and is used for pre- and post-query verification.
• Use table variables.

-- table variable declaration
DECLARE @UserInfoForUpdateVar table (  
   UserId int NOT NULL,
   oldUserNm varchar(255),
   newUserNm varchar(255),
   oldUserAge  int,
   newUserAge  int
);  
 
-- Putting it in a table variable while deleting it.
UPDATE UserInfoForUpdate
   SET UserNm = 'changeName' + UserNm
      ,UserAge = '30'
OUTPUT inserted.UserId,  
       deleted.UserNm,
       inserted.UserNm,
       deleted.UserAge,
       inserted.UserAge  
  INTO @UserInfoForUpdateVar
FROM UserInfoForUpdate     
;  
  
--  Confirm deletion data
SELECT * 
FROM @UserInfoForUpdateVar;

How to use UPDATE for TRY… CATCH blocks.

• Question: Please use TRY CATHCH and TRANSACTION statements to apply UPDATE DATA only when there is no error.
• It is good to understand the error function through the source.
• Try to practice by changing the commit, rollback of the translation.

-- TRY…CATCH  
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 statement precautions

Specify the WHERE clause precisely.

• An UPDATE DML statement can modify any row in a table, so be sure to use the WHERE clause - - correctly to update only the DATA you want.

Use UPDATE DML transaction.

• Because data modification is critical, incorrect updates can compromise data integrity.
• Therefore, use TRY CATHCH and transactions to safely work on update operations.

Take a backup.

• Please back up related data before modifying the data. If an unexpected problem occurs, you must restore through backup data.

Check changed data.

• While executing the UPDATE statement, check the before and after values ??to check the data as expected.

This is the table creation source.

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);