SQL Delete and Truncate Command.
Till so far we have learned how to insert data into a table using
from some other table
so the only thing which is left 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
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 get deleted.
There are 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 set the Index.
- If you accidently 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.
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 gets 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!
SQL Script and Presentation used
You can DOWNLOAD SQL script and presentation used in the Video and in this article.
SQL Script used in Video and in this article
Do Not Drink & Drive Use My Uber Code "UberRebellionRider"
SignUp using my referral code and get first ride worth $20 FREE