SQL DELETE Statement

The DELETE statement is used to delete rows in a table. To do so, we can use the DELETE FROM command. An SQL DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.

Executing a DELETE statement may cause triggers to run that may cause deletes in other tables. For example, if two tables are linked by a foreign key and rows in one table were deleted, then it is common that rows in the second table would also have to be deleted to maintain referential integrity.

Delete from <table> [where <condition>]; 

If the where clause is omitted, all tuples are deleted from the table. An alternative command for deleting all tuples from a table is the truncate table <table> command. However, in this case, the deletions cannot be undone.

Example:

Delete all tuples that have been finished before the actual date (system date):

Delete from EMP where hiredate < sysdate; 

Sysdate is a function in SQL that returns the system date. Another important SQL function is user, which returns the name of the user logged into the current Oracle session.

• Now we will delete the record whose roll=2214 from the stu table.

SQL > DELETE FROM stu 
WHERE ROLL=2214;
 
1 row deleted.
 
• Now we will delete all the records from the stu table.
 
SQL > DELETE FROM stu;            
 OR 
 SQL > DELETE stu; 

Note: Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!