Reputation: 891
How can I TRUNCATE a table
I am using SQLite for my application. when I used truncate command in SQLite it gives me error like " syntax Error near truncate".
Can anyone help me to solve this problem or any other alternative of truncate except DELETE command.
Upvotes: 6
Views: 7587
Answers (4)
Reputation: 107247
The SQLLite Delete Truncation Optimization DELETE FROM {table}; (with no WHERE clause) has similar behaviour to the SQL Server TRUNCATE TABLE {table};. See the SQLLite documentation here
The Truncate Optimization
When the WHERE is omitted from a DELETE statement and the table being deleted has no triggers, SQLite uses an optimization to erase the entire table content without having to visit each row of the table individually. This "truncate" optimization makes the delete run much faster. Prior to SQLite version 3.6.5 (2008-11-12), the truncate optimization also meant that the sqlite3_changes() and sqlite3_total_changes() interfaces and the count_changes pragma will not actually return the number of deleted rows. That problem has been fixed as of version 3.6.5 (2008-11-12).
The truncate optimization can be permanently disabled for all queries by recompiling SQLite with the SQLITE_OMIT_TRUNCATE_OPTIMIZATION compile-time switch.
Upvotes: 1
Reputation: 30775
Why not DELETE?
DELETE with an empty WHERE clause should be pretty fast in SQLite because it does sth called "truncate optimization", see
http://www.sqlite.org/lang_delete.html
Upvotes: 3
Reputation: 31
SQLite DELETE FROM is not equivalent to T-SQL TRUNCATE TABLE.
SQLite DELETE FROM will not reset the index in a field marked autoincrement.
Upvotes: 3
Reputation: 6891
There is none.
Just use DELETE without any arguments.
This is how it is implemented by Drupal 7's database abstraction layer:
Upvotes: 0