Quick PHP Code Tips and Examples

How to Truncate table in SQLite database

June 4th, 2008 by Jon Moffet

I found out that truncating SQLite database is a little bit different from its MySQL counterpart. Here's how to truncate SQLite database from command line

PLAIN TEXT

CODE:

1. sqlite your_database.db
2. sqlite> DELETE FROM some_table;

after that, dont forget to execute VACUUM to compact the database :

PLAIN TEXT

CODE:

1. sqlite> VACUUM;

Bookmark Post:These icons link to social bookmarking sites where readers can share and discover new web pages.

• 
• 
• 
• 
• 
• 
• 
• 

Posted in Uncategorized |

Related Posts

• Using SQLite in PHP 5
• Truncate long text with PHP (excerpt function)
• SQL Injection Examples (Cheatsheet)
• OpenTracker - Lean and mean PHP Bittorrent Tracker
• Convert PHP date() and time into MySQL Date Time Field
• Spam Protected Contact Form example (using Akismet)

7 Responses

1. Pushpesh Says:
   July 7th, 2010 at 5:51 pm

   This took me almost a day to figure out that “TRUNCATE” won’t work in sqlite!…thanks :)

   hope somebody else finds it helpful!

2. PP Says:
   September 17th, 2010 at 4:34 am

   Very important tip: if your table has primary key has AUTOINCREMENT when table is created, SQLITE will behave differently and DELETE FROM tablename; will NOT reset row id to initial value. Because if AUTOINCREMENT is set, SQLite uses different algorithm for calculating auto-increment value so the above won’t work.

3. PP Says:
   September 17th, 2010 at 4:35 am

   I suggest you update main text with this tip.

4. Need help for SQLite Says:
   February 19th, 2011 at 11:15 am

   [...] in SQLite!! no other go we have to use delete….. For further reference check this link : How to Truncate table in SQLite database » Quick PHP Code Tips and Examples -Arvind "Hacking isn’t spreading viruses or causing damage to other computer systems. It [...]

5. Cristine Pauley Says:
   May 12th, 2011 at 7:10 am

   Discovered your blog whilst I was browsing for knowledge for our web site, obviously it’s not all releative however it is funny how you can reach a site by chance and discover yourself looking through something totally different but just as worthwhile. I am inclined to get distracted a multiple times doing this.

6. database - can we use truncate query in android sqlite - Stack Overflow Says:
   August 6th, 2011 at 7:55 pm

   [...] source: http://phpcode.mypapit.net/how-to-truncate-table-in-sqlite-database/49/ [...]

7. Prasildas Says:
   November 4th, 2011 at 7:34 pm

   Hi,
   Is there a way we can truncate the table in SQLite .

Leave a Comment