SQL DELETE Statement in DB2 for i SQL

SQL DELETE Statement in DB2 for i SQL

The DELETE statement is used to delete the existing rows in a table.

DELETE syntax

DELETE FROM table_name WHERE condition

WHERE clause is used with the DELETE statement to specify which records to delete. If we do not use WHERE clause in the DELETE statement then all the rows from the table gets deleted.

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)

--select * from student
ROLLNO   NAME                      SUBJECTID
     1   Ankur                           201
     2   Rahul                           202
     3   Raman                           203
     4   Vimal                           204
     5   Samar                           290

Delete single record

Delete from student        
where rollno = 1            

1 rows deleted from STUDENT in EASYCLASS1

Output:

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

Delete multiple record

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

Delete from student         
where rollno in (2,3)            

 2 rows deleted from STUDENT in EASYCLASS1

Output:

ROLLNO   NAME                      SUBJECTID
     4   Vimal                           204
     5   Samar                           290

Delete Warning

Delete from student

                             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 deleted

2 rows deleted in STUDENT in EASYCLASS1.

Output:

       ROLLNO   NAME                      SUBJECTID
********  End of data  ********