The question:
This might be a dumb question, but I’m new to SQLite and I can’t seem to figure this out. I have 1 table that has columns KEY_ROWID, KEY_NAME, KAY_LATITUDE, and KEY_LONGITUDE. I want the user to be able to select one and delete it; Can anyone give me a direction to start in? My question is in the actual deletion of the row given only its name.
Relevant code:
public class BeaconDatabase {
public static final String KEY_ROWID = "_id";
public static final String KEY_NAME = "beacon_name";
public static final String KEY_LATITUDE = "beacon_lat";
public static final String KEY_LONGITUDE = "beacon_lon";
private static final String DATABASE_NAME ="BeaconDatabase";
private static final String DATABASE_TABLE ="beaconTable";
private static final int DATABASE_VERSION = 1;
private DbHelper helper;
private final Context context;
private SQLiteDatabase db;
public BeaconDatabase(Context context) {
this.context = context;
}
public BeaconDatabase open() {
helper = new DbHelper(this.context);
db = helper.getWritableDatabase();
return this;
}
public void close() {
helper.close();
}
public long createEntry(String name, Double lat, Double lon) {
ContentValues cv = new ContentValues();
cv.put(KEY_NAME, name);
cv.put(KEY_LATITUDE, lat);
cv.put(KEY_LONGITUDE, lon);
return db.insert(DATABASE_TABLE, null, cv);
}
public void deleteEntry(long row) {
// Deletes a row given its rowId, but I want to be able to pass
// in the name of the KEY_NAME and have it delete that row.
//db.delete(DATABASE_TABLE, KEY_ROWID + "=" + row, null);
}
public String getData() {
String[] columns = { KEY_ROWID, KEY_NAME, KEY_LATITUDE, KEY_LONGITUDE };
Cursor cursor = db.query(DATABASE_TABLE, columns, null, null, null, null, null);
String result = "";
int iRow = cursor.getColumnIndex(KEY_ROWID);
int iName = cursor.getColumnIndex(KEY_NAME);
int iLat = cursor.getColumnIndex(KEY_LATITUDE);
int iLon = cursor.getColumnIndex(KEY_LONGITUDE);
for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
result += cursor.getString(iRow) + ": " + cursor.getString(iName) + " - " + cursor.getDouble(iLat) + " latitude " + cursor.getDouble(iLon) + " longituden";
}
return result;
}
private static class DbHelper extends SQLiteOpenHelper {
public DbHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
KEY_NAME + " TEXT NOT NULL, " +
KEY_LATITUDE + " DOUBLE, " +
KEY_LONGITUDE + " DOUBLE);"
);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
onCreate(db);
}
}
}
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
You can try like this:
//---deletes a particular title---
public boolean deleteTitle(String name)
{
return db.delete(DATABASE_TABLE, KEY_NAME + "=" + name, null) > 0;
}
or
public boolean deleteTitle(String name)
{
return db.delete(DATABASE_TABLE, KEY_NAME + "=?", new String[]{name}) > 0;
}
Method 2
Try like that may you get your solution
String table = "beaconTable";
String whereClause = "_id=?";
String[] whereArgs = new String[] { String.valueOf(row) };
db.delete(table, whereClause, whereArgs);
Method 3
it’s better to use whereargs too;
db.delete("tablename","id=? and name=?",new String[]{"1","jack"});
this is like useing this command:
delete from tablename where id='1' and name ='jack'
and using delete function in such way is good because it removes sql injections.
Method 4
Till i understand your question,you want to put two conditions to select a row to be deleted.for that,you need to do:
public void deleteEntry(long row,String key_name) {
db.delete(DATABASE_TABLE, KEY_ROWID + "=" + row + " and " + KEY_NAME + "=" + key_name, null);
/*if you just have key_name to select a row,you can ignore passing rowid(here-row) and use:
db.delete(DATABASE_TABLE, KEY_NAME + "=" + key_name, null);
*/
}
Method 5
Try this code
public void deleteRow(String value)
{
SQLiteDatabase db = this.getWritableDatabase();
db.execSQL("DELETE FROM " + TABLE_NAME+ " WHERE "+COlUMN_NAME+"='"+value+"'");
db.close();
}
Method 6
Try this code…
private static final String mname = "'USERNAME'";
public void deleteContact()
{
db.delete(TABLE_CONTACTS, KEY_NAME + "=" + mname, null);
}
Method 7
This works perfectly:
public boolean deleteSingleRow(String rowId)
{
return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
}
You can also refer to this example.
Method 8
if you are using SQLiteDatabase then there is a delete method
Definition of Delete
int delete (String table, String whereClause, String[] whereArgs)
Example Implementation
Now we can write a method called delete with argument as name
public void delete(String value) {
db.delete(DATABASE_TABLE, KEY_NAME + "=?", new String[]{String.valueOf(value)});
}
if you want to delete all records then just pass null to the above method,
public void delete() {
db.delete(DATABASE_TABLE, null, null);
}
Method 9
Guys this is a generic method you can use for all your tables, Worked perfectly in my case.
public void deleteRowFromTable(String tableName, String columnName, String keyValue) {
String whereClause = columnName + "=?";
String[] whereArgs = new String[]{String.valueOf(keyValue)};
yourDatabase.delete(tableName, whereClause, whereArgs);
}
Method 10
To delete rows from a table, you need to provide selection criteria that identify the rows to the delete() method. The mechanism works the same as the selection arguments to the query() method. It divides the selection specification into a selection clause(where clause) and selection arguments.
SQLiteDatabase db = this.getWritableDatabase();
// Define 'where' part of query.
String selection = Contract.COLUMN_COMPANY_ID + " =? and "
+ Contract.CLOUMN_TYPE +" =? ";
// Specify arguments in placeholder order.
String[] selectionArgs = { cid,mode };
// Issue SQL statement.
int deletedRows = db.delete(Contract.TABLE_NAME,
selection, selectionArgs);
return deletedRows;// no.of rows deleted.
The return value for the delete() method indicates the number of rows that were deleted from the database.
Method 11
Guys if above solutions don’t work for you then try this one also because it worked for me.
public boolean deleteRow(String name)
{
return db.delete(DATABASE_TABLE, KEY_NAME + "='" + name +"' ;", null) > 0;
}
Method 12
Works great!
public void deleteNewMelk(String melkCode) {
getWritableDatabase().delete(your_table, your_column +"=?", new String[]{melkCode});
}
Method 13
Try this one:
public void deleteEntry(long rowId) {
database.delete(DATABASE_TABLE , KEY_ROWID
+ " = " + rowId, null);}
Method 14
public boolean deleteRow(long l) {
String where = "ID" + "=" + l;
return db.delete(TABLE_COUNTRY, where, null) != 0;
}
Method 15
You can do something like this, sharing my working code snippet
Make sure query is like this
DELETE FROM tableName WHERE
KEY__NAME = ‘parameterToMatch’
public void removeSingleFeedback(InputFeedback itemToDelete) {
//Open the database
SQLiteDatabase database = this.getWritableDatabase();
//Execute sql query to remove from database
//NOTE: When removing by String in SQL, value must be enclosed with ''
database.execSQL("DELETE FROM " + TABLE_FEEDBACKS + " WHERE "
+ KEY_CUSTMER_NAME + "= '" + itemToDelete.getStrCustName() + "'" +
" AND " + KEY_DESIGNATION + "= '" + itemToDelete.getStrCustDesignation() + "'" +
" AND " + KEY_EMAIL + "= '" + itemToDelete.getStrCustEmail() + "'" +
" AND " + KEY_CONTACT_NO + "= '" + itemToDelete.getStrCustContactNo() + "'" +
" AND " + KEY_MOBILE_NO + "= '" + itemToDelete.getStrCustMobile() + "'" +
" AND " + KEY_CLUSTER_NAME + "= '" + itemToDelete.getStrClusterName() + "'" +
" AND " + KEY_PRODUCT_NAME + "= '" + itemToDelete.getStrProductName() + "'" +
" AND " + KEY_INSTALL_VERSION + "= '" + itemToDelete.getStrInstalledVersion() + "'" +
" AND " + KEY_REQUIREMENTS + "= '" + itemToDelete.getStrRequirements() + "'" +
" AND " + KEY_CHALLENGES + "= '" + itemToDelete.getStrChallenges() + "'" +
" AND " + KEY_EXPANSION + "= '" + itemToDelete.getStrFutureExpansion() + "'" +
" AND " + KEY_COMMENTS + "= '" + itemToDelete.getStrComments() + "'"
);
//Close the database
database.close();
}
Method 16
Try the below code-
mSQLiteDatabase = getWritableDatabase();//To delete , database should be writable.
int rowDeleted = mSQLiteDatabase.delete(TABLE_NAME,id + " =?",
new String[] {String.valueOf(id)});
mSQLiteDatabase.close();//This is very important once database operation is done.
if(rowDeleted != 0){
//delete success.
} else {
//delete failed.
}
Method 17
The only way that worked for me was this
fun removeCart(mCart: Cart) {
val db = dbHelper.writableDatabase
val deleteLineWithThisValue = mCart.f
db.delete(cons.tableNames[3], Cart.KEY_f + " LIKE '%" + deleteLineWithThisValue + "%' ", null)
}
class Cart {
var a: String? = null
var b: String? = null
var c: String? = null
var d: String? = null
var e: Int? = null
var f: String? = null
companion object {
// Labels Table Columns names
const val rowIdKey = "_id"
const val idKey = "id"
const val KEY_a = "a"
const val KEY_b = "b"
const val KEY_c = "c"
const val KEY_d = "d"
const val KEY_e = "e"
const val KEY_f = "f"
}
}
object cons {
val tableNames = arrayOf(
/*0*/ "shoes",
/*1*/ "hats",
/*2*/ "shirt",
/*3*/ "car"
)
}
Method 18
But the Accepted answer Not worked for me. I think String should be In SQL, strings must be quoted. So, in my case this worked for me against the accepted answer :
database.delete(TABLE_DAILY_NOTES, WORK_ENTRY_CLUMN_NAME + "='" + workEntry+"'", null);
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0