Till so far we have learned how to insert data into a table using INSERT statement, SQL Developer and from some other table so the only thing which is left now is how to delete data from a table.
Among several different ways of deleting data from a table the two most used ways are SQL Delete and Truncate command. Although both perform the same task yet they are still different. In this blog you will learn how to use SQL Delete and truncate command as well as the various differences between them.
SQL DELETE command
SQL Delete is a DML command which removes entire rows of data from a specified table or view.
DELETE FROM table_name WHERE condition;
As it’s a DELETE query thus it starts with DELETE keyword followed by FROM keyword. After that you have to specify the table name from which you want to delete the data and at the end we have our WHERE clause. WHERE clause enables you to choose rows from the references table or view that need to be deleted conditionally. That is only those rows that meet these search conditions are deleted. If you omit the WHERE clause, all rows in the table or view are deleted.
Query 1 : Let’s say you want to delete only those rows from employees table of HR schema where job id is ST_CLERK
DELETE FROM employees WHERE job_id = ‘ST_CLERK’;
This query will delete all the rows from employees table where job id is st clerk.
Query 2 :Now, what if, you want to delete all the rows from the employees table? For that we just need to do a slight modification in the above query.
DELETE FROM employees;
As I mentioned above If we use DELETE command without the WHERE clause, all the rows in the table or view are deleted. On executing this query all the rows from employee table will be deleted.
There are a few things which you should know about SQL DELETE command:
- DELETE is a DML command.
- Whenever you perform DELETE operation all the triggers associated with DELETE command gets executed.
- DELETE checks all the constraints on all the columns of the rows which are getting deleted before deleting the rows and accordingly sets the Index.
- If you accidentally deleted some data then there are still chances that you can get it back by performing ROLLBACK command. Roll back will not work if you have already performed the ‘commit’.
SQL Truncate Command
Truncate command deletes all the rows from a table.
For example say you want to delete all the rows from countries table of HR schema. The syntax for that will be
TRUNCATE TABLE countries;
Things to remember:
- Truncate is a DDL command.
- You cannot roll back a TRUNCATE statement.
- You cannot use WHERE clause with TRUNCATE statement. This means that unlike SQL DELETE there are no options for conditional delete.
- Unlike SQL delete when you perform Truncate no trigger gets executed.
- Truncate doesn’t use much UNDO space as SQL Delete.
- Truncate is significantly faster than the SQL Delete. This is because when you use DELETE, all the data first gets copied onto the Undo Tablespace after which the delete operation is performed. That’s why when you type ROLLBACK after deleting a table; you can get back the data. All this process takes time. But when you type TRUNCATE, it removes data directly without copying it onto the Rollback Tablespace. That’s why TRUNCATE is faster. Once you truncate you can’t get back the data.
That’s all folks. Hope I was successful in explaining the concept to you. Please do share this page on your social media. Thanks & Have a great day!