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.