Delete
Difference between Delete, Truncate and Drop
- Delete is used to delete a particular row or all the row using the where clause or without using it .
- Syntax for delete command is: DELETE FROM table_name [WHERE condition];
- Syntax for delete command without where clause. DELETE FROM table_name.
- Note in the second case all the row from the table will be deleted.
- Truncate is used to delete all the row from the tableand free the space containing the table.
- Syntax to truncate a table : TRUNCATE TABLE table_name;
- DROP command is used to remove an object from the database. If you drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back.
- Syntax for Drop command:
Difference between Delete, Truncate and Drop
Delete | Truncate | Drop |
Deletes command delete only rows from the table based on the condition given in the where clause or deletes all the rows from the table if no condition is specified. But it does not free the space containing the table. | This command is used to delete all the rows from the table and free the space containing the table.If a table is truncated, the table structure remains the same. | If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if want use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. |
No comments:
Post a Comment