>> DELETE DROP and TRUNCATE in SQL.

DELETE

To delete all the data or partial data from a table . This will delete data only, not the table.

Suppose we have a table EMP_INFO as below :

NAME	DEPARTMENT	SALARY	LOCATION	POSITION	STATE	JOINDATE
DAVID	MARKETING	(null)	CHICAGO	MANAGER	IL	12-Dec-00
RICHARD	SALES	1000	BALTIMORE	SALESMAN	MD	1-Jan-08
SMITH	RESOURCES	1500	DALLAS	HR	TX	5-Jan-06
RAM	RESOURCES	1200	DALLAS	ASSISTANT	TX	5-Jun-06
GOPI	SALES	(null)	AUSTIN	MARKETER	TX	9-Feb-08
BRITNI	TECHNICAL	1800	BALTIMORE	ASSISTANT	MD	1-Feb-09
ADAM	TECHNICAL	2200	BALTIMORE	LEAD	MD	(null)
MIKE	EXECUTIVE	2500	CHICAGO	DIRECTOR	IL	1-Aug-10
JAMES	MARKETING	1200	RESTON	ACCOUNTANT	VA	20-Aug-14
JULI	EXECUTIVE	2200	ARLINGTON	MANAGER	VA	21-Aug-12

 To delete Partial data:

Suppose we want to delete the data with "POSITION" = "ASSISTANT" from the table.

Syntax:

DELETE  FROM  {TABLE NAME}  WHERE  {CONDITION} ;

Query:

DELETE FROM  EMP_INFO  WHERE  POSITION= 'ASSISTANT';  -- when we run this query , it will display : 2 rows deleted.(As we have two rows for the POSITION= 'ASSISTANT').

Now, let's check the data in the table EMP_INFO :

SELECT * FROM EMP_INFO ;  -- This query will display below data:

NAME	DEPARTMENT	SALARY	LOCATION	POSITION	STATE	JOINDATE
DAVID	MARKETING	(null)	CHICAGO	MANAGER	IL	12-Dec-00
RICHARD	SALES	1000	BALTIMORE	SALESMAN	MD	1-Jan-08
SMITH	RESOURCES	1500	DALLAS	HR	TX	5-Jan-06
GOPI	SALES	(null)	AUSTIN	MARKETER	TX	9-Feb-08
ADAM	TECHNICAL	2200	BALTIMORE	LEAD	MD	(null)
MIKE	EXECUTIVE	2500	CHICAGO	DIRECTOR	IL	1-Aug-10
JAMES	MARKETING	1200	RESTON	ACCOUNTANT	VA	20-Aug-14
JULI	EXECUTIVE	2200	ARLINGTON	MANAGER	VA	21-Aug-12

To delete all the data from the table :

Syntax :

DELETE  FROM  {TABLE NAME} ;

Query:

DELETE  FROM  EMP_INFO;  -- when we run this query, it shows : 8 rows deleted. (where 8 is the total number of rows/records in the table).

Now, let's check the table :

SELECT * FROM EMP_INFO;  -- This will return the column headers only  without data  as below :

NAME

DEPARTMENT

SALARY

LOCATION

POSITION

STATE

JOINDATE

TRUNCATE :

Truncate will delete all the data from the table  same like : DELETE  FROM  {TABLE NAME} ;

However, when we use DELETE, the deleted data can be rolled back (restored)  whereas when we use TRUNCATE,  the truncated data can not be rolled back.

For example:

We deleted all the data from the table EMP_INFO above , now we can  regain the deleted data(rollback the delete operation) as below:

ROLLBACK TRANSACTION;  -- after running this , we see the message :  Rollback complete.

Now, let's see the data in the table EMP_INFO :

SELECT * FROM EMP_INFO;  -- This shows below data:

NAME	DEPARTMENT	SALARY	LOCATION	POSITION	STATE	JOINDATE
DAVID	MARKETING		CHICAGO	MANAGER	IL	12-Dec-00
RICHARD	SALES	1000	BALTIMORE	SALESMAN	MD	1-Jan-08
SMITH	RESOURCES	1500	DALLAS	HR	TX	5-Jan-06
GOPI	SALES		AUSTIN	MARKETER	TX	9-Feb-08
ADAM	TECHNICAL	2200	BALTIMORE	LEAD	MD
MIKE	EXECUTIVE	2500	CHICAGO	DIRECTOR	IL	1-Aug-10
JAMES	MARKETING	1200	RESTON	ACCOUNTANT	VA	20-Aug-14
JULI	EXECUTIVE	2200	ARLINGTON	MANAGER	VA	21-Aug-12

But when we TRUNCATE the table, the data will be deleted forever and cannot be rolled back.

Syntax of TRUNCATE:

TRUNCATE  TABLE  {TABLE NAME};

Query:

TRUNCATE  TABLE  EMP_INFO;

When this query is executed, we see this result : Table  EMP_INFO  truncated.

Now, let's check if the table exist.

SELECT * FROM  EMP_INFO;  -- This will retrun below result , only the column headers are displayed without data:

NAME

DEPARTMENT

SALARY

LOCATION

POSITION

STATE

JOINDATE

Let's try to rollback.

ROLLBACK TRANSACTION;  -- after running this , we see the message :  Rollback complete.

Now, let's see the data in the table  EMP_INFO :

SELECT  *  FROM  EMP_INFO;  -- We still see below result, the data is not rolled back.

NAME

DEPARTMENT

SALARY

LOCATION

POSITION

STATE

JOINDATE

DROP

Drop will delete the whole table (the table structure and data everything will be deleted for ever) and cannot be rolled back.

Suppose we have a table , TEST  as below:

NAME	DEPT	SALARY
David	Marketing	2000
Smith	HR	3000
Mina	Staffing	(null)

Now, let's use the  DROP  command.

Syntax:

DROP  TABLE  {TABLE_NAME};

Query:

DROP   TABLE   TEST;  -- When we run this query, we get the message : Table  TEST  dropped.

Now, let's try below query:

SELECT  *  FROM   TEST;

After running  this query, we get message like this : "table or view does not exist". That means the table has been deleted and the table does not exist.

So be careful  before dropping a table, it removes the complete table information.

Difference between Delete, Drop and Truncate:

	Delete	Drop	Truncate
1	Delete will remove data(selected rows or all the rows ) from the table	Drop will remove the table, table structure , data all rows, columns, indexes, references, privileges from the database.	Truncate will delete all the rows of data from the table.
2	Delete is DML command	Drop is DDL command	Truncate is a DDL command
3	Delete operation can be rolled back ,ie, deleted data can be restored. That is why it takes more space. But once committed, can not be rolled back.	Dropped operation can not be rolled back , ie, dropped table can not be restored.	Truncated operation can not be rolled back , ie, truncated data can not be restored, that is why it takes less space than Delete.
4	Slower operation	Faster operation	Faster operation
5	Trigger can be fired	Trigger can not be fired	Trigger can not be fired

NEXT

Go To Main SQL Page