Actually, I seem to recall a discussion that revealed that "DELETE FROM Foo" *does* truncate the table. The discussion came up because someone complained that "ON DELETE" triggers were not being fired; the explanation was that 'DELETE FROM Foo" simply drops and re-creates Foo. The solution was to rewrite the code as "DELETE FROM Foo WHERE 1=1" or something similar.
This was pretty easy to verify: sqlite> explain delete from foo; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 explain delete from foo; 00 1 Goto 0 4 0 00 2 Clear 2 0 0 foo 00 3 Halt 0 0 0 00 4 Transaction 0 1 0 00 5 VerifyCookie 0 1 0 00 6 Goto 0 2 0 00 sqlite> explain delete from foo where 1=1; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Trace 0 0 0 explain delete from foo where 1=1; 00 1 Goto 0 23 0 00 2 Integer 1 2 0 00 3 Integer 1 3 0 00 4 Ne 3 14 2 6a 5 OpenRead 0 2 0 00 6 SetNumColumns 0 0 0 00 7 Rewind 0 14 0 00 8 Integer 1 3 0 00 9 Integer 1 2 0 00 10 Ne 2 13 3 6a 11 Rowid 0 1 0 00 12 FifoWrite 1 0 0 00 13 Next 0 8 0 00 14 Close 0 0 0 00 15 OpenWrite 0 2 0 00 16 SetNumColumns 0 2 0 00 17 FifoRead 1 21 0 00 18 NotExists 0 20 1 00 19 Delete 0 1 0 foo 00 20 Goto 0 17 0 00 21 Close 0 0 0 00 22 Halt 0 0 0 00 23 Transaction 0 1 0 00 24 VerifyCookie 0 1 0 00 25 TableLock -1 2 0 foo 00 26 Goto 0 2 0 00 Heading over to http://www.sqlite.org/cvstrac/fileview?f=sqlite/src/vdbe.c&v=1.741 and looking at OP_Clear reveals that "DELETE FROM Foo" is closest in implementation to "TRUNCATE TABLE Foo". On Wed, May 21, 2008 at 2:46 AM, Harold Wood & Meyuni Gani < [EMAIL PROTECTED]> wrote: > Doesn't sqlite support the truncate table command > > Woody > from his pda > > -----Original Message----- > From: Carlo S. Marcelo <[EMAIL PROTECTED]> > Sent: Tuesday, May 20, 2008 8:49 PM > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Subject: Re: [sqlite] deleting 100,000 entries > > @Barefoot and Keith, > > Awesome! It took forever for the web interface to delete, and I had to > restart httpd for the database to unlock.. the command you provided took > less than a second to clear everything out, thanks! > > Carlo > > ----- Original Message ---- > From: BareFeet <[EMAIL PROTECTED]> > To: General Discussion of SQLite Database <sqlite-users@sqlite.org> > Sent: Wednesday, May 21, 2008 11:45:06 AM > Subject: Re: [sqlite] deleting 100,000 entries > > Hi Carlo, > > > I want to clear the whole thing out(list)! > > Do you mean that you want to delete all rows from the "list" table? If > so, do this: > > delete from list; > > See the syntax pages at: > http://www.sqlite.org/lang.html > http://www.sqlite.org/lang_delete.html > > Tom > BareFeet > http://www.tandb.com.au/sqlite/compare/?ml > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
