One of the questions I got asked today is what the difference is between the SQL commands DELETE and TRUNCATE.

They both are used to empty out a table, which is different from DROP (which deletes the table)

 

DELETE

DELETE FROM <table_name>
DELETE FROM <table_name> <where_clause>

Delete is used to remove rows from a table, it can remove all rows or you can specify a where clause to delete only certain rows. A COMMIT needs to be done after a delete and all delete triggers in the table will be fired.

DELETE FROM table1 WHERE column1=’value1′;
10 rows delete.

COMMIT;
commit complete.

TRUNCATE

TRUNCATE TABLE <table_name>

Truncate is used to delete all rows from a table. It does not fire any triggers and there is no rollback. A truncate is therefore much faster than a delete

TRUNCATE TABLE table1;
table truncated.

 

Edwin Kwan is the founder and editor of Little Handy Tips and Wollongong Fitness. He is also the developer for the Google Custom Search WordPress plugin and Custom About Author WordPress plugin. He also has a keen interest in Photography . Find out more about him here.

Google+