Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 13-02-2009
Member
 
Join Date: Feb 2009
Posts: 8
What is the difference between delete table & truncate table?

Hi,
What is the difference between delete table & truncate table?

Please let me know!
Reply With Quote
  #2  
Old 13-02-2009
Member
 
Join Date: May 2008
Posts: 2,383
Re: What is the difference between delete table & truncate table?

The DELETE command is used to remove rows from a table. A WHERE clause can be used to only remove some rows. If no WHERE condition is specified, all rows will be removed. After performing a DELETE operation you need to COMMIT or ROLLBACK the transaction to make the change permanent or to undo it. Note that this operation will cause all DELETE triggers on the table to fire.

Code:
SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
----------
        14

SQL> DELETE FROM emp WHERE job = 'CLERK';

4 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
TRUNCATE

TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUNCATE is faster and doesn't use as much undo space as a DELETE.

Code:
SQL> TRUNCATE TABLE emp;

Table truncated.

SQL> SELECT COUNT(*) FROM emp;

  COUNT(*)
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
  #3  
Old 13-02-2009
Member
 
Join Date: Feb 2008
Posts: 1,848
Re: What is the difference between delete table & truncate table?

Hi,

The Difference between TRUNCATE and DELETE commands.

TRUNCATE

1. Once u use TRUNCATE command then u cannot retrieve the data again from the table.
2. TRUNCATE removes all the rows from the Table.

DELETE

1. If u use DELETE Command then the data deleted can be retrieved when you ROLLBACK .
2. U can delete selected no of records from table using DELETE command.

For Ex:

DELETE FROM [TABLE_NAME];, deletes all the records from the table.
DELETE FROM [TABLE_NAME] WHERE [FieldName] > 10;, will delete records which have the higher values than 10 ..i.e works on the specified condition in the WHERE CLAUSE.

Hope got answer !
Reply With Quote
  #4  
Old 13-02-2009
Member
 
Join Date: Jan 2008
Posts: 1,515
Re: What is the difference between delete table & truncate table?

TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server.
DELETE is a DML command and can be rolled back.

Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.

TRUNCATE : You can't use WHERE clause
DELETE : You can use WHERE clause
Reply With Quote
  #5  
Old 13-02-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
Re: What is the difference between delete table & truncate table?

DELETE and TRUNCATE differ in some more important and subtle ways than the auto increment counter.

The most important difference is DELETE operations are transaction-safe and logged, which means DELETEs can be rolled back. TRUNCATE cannot be done inside a transaction and can?t be rolled back. Because TRUNCATE is not logged recovering a mistakenly TRUNCATEd table is a much bigger problem than recovering from a DELETE.

DELETE will fail if foreign key constraints are broken; TRUNCATE may not honor foreign key constraints (it does for InnoDB tables). DELETE will fire any ON DELETE triggers; TRUNCATE will not.

TRUNCATE is probably better thought of as a shortcut for DROP TABLE/CREATE TABLE rather than a quirky kind of DELETE.

These differences are not specific to mySQL ? PostgreSQL, MS SQL Server, and Oracle behave more or less the same way.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "What is the difference between delete table & truncate table?"
Thread Thread Starter Forum Replies Last Post
Unable to work with table columns in opera browser when the table has wide layout Janya Technology & Internet 5 27-08-2011 10:53 AM
Link a Table to another Table to Drop Down In Main Table himeshRES Windows Software 6 11-12-2010 02:01 PM
Colonizing a table of employees from a table of applicants in Microsoft Access laplapye Windows Software 4 30-11-2010 03:01 AM
Difference between Delete and Truncate Aileen_L Software Development 3 29-01-2010 09:05 AM
Delete the entries of Table 1 according to criterion of other table Wguy2008 Software Development 2 24-04-2009 11:13 PM


All times are GMT +5.5. The time now is 01:14 PM.