CODEWITHSUNDEEP
javadatabasesqlite
javajoejuan
javajoejuan

Reputation: 343

Getting SQLite to delete all records in a table

I’m trying to delete all records in my MAIN_TABLE in my database located in my C:/ drive.. I’m using SQLite and I’m not on the Android platform. I’ve only seen TURNCATE used in a couple of examples so I’m not 100% sure that is right..

CODE:

package Exporter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class DeleteAllRecords { 
//public void   runDeleteAllRecords()throws Exception, SQLException {
  public static void main(String[] argv) throws Exception {

String driverName = "org.sqlite.JDBC";
Class.forName(driverName);

String url = ("jdbc:sqlite:" + "C:/Test/DATABASE.db");
Connection connection = DriverManager.getConnection(url);
Statement stmt = connection.createStatement();

String sql = "TRUNCATE MAIN_TABLE";
stmt.executeUpdate(sql);
sql = "DELETE FROM MAIN_TABLE";
stmt.executeUpdate(sql);
}
}

ERRORS:

Exception in thread "main" java.sql.SQLException: near "TRUNCATE": syntax error
at org.sqlite.core.NativeDB.throwex(NativeDB.java:397)
at org.sqlite.core.NativeDB._exec(Native Method)
at org.sqlite.jdbc3.JDBC3Statement.executeUpdate(JDBC3Statement.java:116)
at Exporter.DeleteAllRecords.main(DeleteAllRecords.java:21)

Upvotes: 8

Views: 13671

Answers (3)

dheeraj reddy
dheeraj reddy

Reputation: 1197

db.execSQL("delete from "+ TABLENAME);

don't put star

Upvotes: 0

startup.maker
startup.maker

Reputation: 81

"DELETE FROM MAIN_TABLE" maybe is not enough. Your data only marked as deleted but physically still in database file.

If you want zeros in place of your old data - you need second command "VACUUM".

approximate code:

String sql = "DELETE FROM MAIN_TABLE";
stmt.executeUpdate(sql);
sql = "VACUUM";
stmt.executeUpdate(sql);

Upvotes: 4

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521168

SQLite does not have an actual TRUNCATE command. Rather it has a truncate optimizer which will be used whenever you run DELETE without a WHERE clause. So you should just use DELETE FROM without a WHERE clause to truncate your table:

String sql = "DELETE FROM MAIN_TABLE";
stmt.executeUpdate(sql);

From the documentation:

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.

Upvotes: 11

Related Questions