Home / mySQL / Difference between DELETE, TRUNCATE and DROP commands

Difference between DELETE, TRUNCATE and DROP commands

Good Morning to all,
In here, I share the article related to delete, truncated and drop command. We cover basic concept of these three command, syntax, simple example in this post.



DELETE

If you want to remove the rows from a table the DELETE command helps to perform this operations. DELETE is a Data Manipulation Language (DML).
In DELETE command we use WHERE clause, it can be used to only remove some rows. If there is no WHERE clause,then remove all rows. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it.

Syntax of a SQL DELETE Statement


DELETE FROM tablename [WHERE condition];

To delete all rows


DELETE FROM tablename

OR





DELETE * FROM tablename

Note:tablename — the table name which has to be updated. WHERE clause is optional in DELETE command.

Example


SQL> SELECT COUNT(*) FROM employee;

COUNT(*)
----------
18

SQL> DELETE FROM employee WHERE position = 'developer';

5 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM employee;

COUNT(*)
----------
13





TRUNCATE

TRUNCATE helps to remove all the rows from a table.
The operation cannot be rolled back and no triggers will be fired. TRUNCATE is a Data Definition Language( DDL).

Syntax to TRUNCATE a table:


TRUNCATE TABLE tablename;

Example


SQL> TRUNCATE TABLE employee;

Table truncated.

SQL> SELECT COUNT(*) FROM employee;

COUNT(*)
----------
0

DELETE vs TRUNCATE

DELETE TRUNCATE
It’s a DML command It’s a DDL command
DELETE removes some rows if WHERE clause is used Removes all rows from a table, but the table structures and its columns, constraints, indexes remains.
Removes Some or All rows from a table. Removes All rows from a table.
Can be rolled back Cannot be rolled back
Does not reset identity of the table Resets the identity of the table.
A WHERE clause can be used to remove some rows. If no WHERE condition is specified, all rows will be removed. Does not require a WHERE clause, so you can not filter rows while Truncating.
Triggers will be fired. No Triggers will be fired.
The WHERE condition is optional Cannot use WHERE condition

DROP

The DROP command removes a table from the database. All the tables’ rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.
DROP is a Data Definition Language( DDL).

Syntax to drop a sql table structure:






DROP TABLE tablename;

Example


SQL> DROP TABLE employee;

Table dropped.

SQL> SELECT * FROM employee;
SELECT * FROM employee
*
ERROR at line 1:
ORA-00942: table or view does not exist




If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

Loading Facebook Comments ...
Social Media Auto Publish Powered By : XYZScripts.com