SQL UPDATE Statement in DB2 for i SQL

SQL UPDATE Statement in DB2 for i SQL

The UPDATE statement is used to modify the existing records in a table or a view.

UPDATE syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition

WHERE clause is used in UPDATE statement to select the record to be updated. Therefore, if we missed mentioning WHERE clause in an UPDATE statement then we might end up updating all the records in the table unless that is not asked.

Create table STUDENT

-- Create Table
CREATE TABLE Student (rollno integer, 
Name char(20),                        
subjectid integer) 

--Insert Values
INSERT INTO Student VALUES
(1,'Ankur',201),          
(2,'Rahul',202),          
(3,'Raman',203),          
(4,'Vimal',204),
(5,'Samar',290)

Update single record

update student 
set name = 'CHGNAME'        
where rollno = 1            

 1 rows updated in STUDENT in EASYCLASS1

Output:

ROLLNO   NAME                      SUBJECTID
     1   CHGNAME                         201
     2   Rahul                           202
     3   Raman                           203
     4   Vimal                           204
     5   Samar                           290

Update multiple record

WHERE clause condition decides the number of records to be updated. Number of rows satisfies the condition in the where clause gets updated.

update student 
set name = 'CHGNAME'        
where rollno in (2,4)            

 2 rows updated in STUDENT in EASYCLASS1

Output:

ROLLNO   NAME                      SUBJECTID
     1   CHGNAME                         201
     2   CHGNAME                         202
     3   Raman                           203
     4   CHGNAME                         204
     5   Samar                           290

Update Warning

update student 
set name = 'CHGNAME'     

                             Confirm Statement                               
                                                                             
You are about to alter (DELETE or UPDATE) all of the records in your file(s).
                                                                             
Press Enter to confirm your statement to alter the entire file.              
Press F12=Cancel to return and cancel your statement.                        
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
                                                                             
F12=Cancel                                                                   

If we press ENTER, all the rows in the table got updated

5 rows updated in STUDENT in EASYCLASS1.

Output:

ROLLNO   NAME                      SUBJECTID
     1   NEWNAME                         201
     2   NEWNAME                         202
     3   NEWNAME                         203
     4   NEWNAME                         204
     5   NEWNAME                         290

Update multiple columns

After SET statement we use comma to separate each update column/value pair to be updated.

update student 
set name = 'CHGNAME', Subjectid = 502        
where rollno =2            

 1 rows updated in STUDENT in EASYCLASS1

Output:

ROLLNO   NAME                      SUBJECTID
     1   NEWNAME                         201
     2   CHGNAME                         502
     3   NEWNAME                         203
     4   NEWNAME                         204
     5   NEWNAME                         290