-4

Delete a single row of a table in MYSQL using truncate command difference between truncate and delete we can delete a single row but how to delete a single row using truncate?

vaibhav
  • 396
  • 2
  • 17
user3480743
  • 1
  • 1
  • 1
  • 3
    `TRUNCATE` is a **table** command. Next time, [RTFM](http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html) – Phil May 27 '14 at 06:43
  • 1
    Possible duplicate of [What's the difference between delete from table_a and truncate table table_a in MySQL?](http://stackoverflow.com/questions/1796288/whats-the-difference-between-delete-from-table-a-and-truncate-table-table-a-in) – Ben May 27 '14 at 06:45

5 Answers5

2

You can't truncate a single row. Read up on the mysql reference:

TRUNCATE [TABLE] tbl_name TRUNCATE TABLE empties a table completely. It requires the DROP privilege as of MySQL 5.1.16. (Before 5.1.16, it requires the DELETE privilege).

Logically, TRUNCATE TABLE is equivalent to a DELETE statement that deletes all rows, but there are practical differences under some circumstances.

Reference:

Arion
  • 31,011
  • 10
  • 70
  • 88
2

TRUNCATE removes all the rows from the Table.

  1. DELETE statement deletes table rows and returns number of rows deleted.we can delete selected no of records from table using DELETE command.

  2. TRUNCATE drops the table and re-create it. It is much faster than deleting rows one by one. Once u use TRUNCATE command then u cannot retrieve the data again from the table.TRUNCATE removes all the rows from the Table.

  3. Drop - deletes the data as well as structure.

The difference between DROP and DELETE table is that, after executing DELETE statement the contents of table are removed but the structure remains same, but in case of DROP statement both the contents and structure are removed.

Deepu Sasidharan
  • 5,193
  • 10
  • 40
  • 97
1

When you want to delete a single row you use Delete command. When you want to clear the entire table data you can use Truncate Table command.

Thanos Markou
  • 2,587
  • 3
  • 25
  • 32
1

Just for your knowledge DELETE is a DML Command. and TRUNCATE is a DDL command

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
1

No truncate can't be used for one single row. It has to be used for the entire table.

proper working/practical differences are :

  1. With Delete command structure of the table is not purged from database.
  2. you can delete all the data of the database but not the format of the table. that remains stable.
  3. Delete is a DML command.

  4. DELETE handles rows chosen with a WHERE statement. Its use is part of the discipline of running production applications. For example, you might DELETE all rows that have been marked "complete" more than a month ago.

  5. Truncate removes/purge the structure of the table itself. It also purges sets the auto_increment to its initial value (that is zero by default).

  6. Truncate is a DDL command
  7. TRUNCATE rapidly removes all rows from a table while maintaining the table definition. (Row by row DELETE can take time, especially for tables with lots of keys.)
  8. It comes in handy for such things as log tables that start out empty for each week's production.
  9. It has the convenient side effect of resetting the indexes and releasing unused disk storage. I have used TRUNCATE to wipe out the contents of a log table that used to contain millions of rows, to switch to an operational discipline where it only contains a weeks' worth of rows, for example.
vaibhav
  • 396
  • 2
  • 17