CODEWITHSUNDEEP
androidsqlite
Joeliomason
Joeliomason

Reputation: 159

Delete similar records in SQLite android

I am having trouble figuring out how to delete records from a table in an SQLite database where there is nothing unique about the records, so what I mean is that I have a table where there is a lot of deliberate duplication.

My app is an Exercise Tracker app where you can store exercises with sets, reps and weight, the table I am trying to go into and delete a record is the one where it stores reps and weight, so they could easily be the same.

Here is my code: 

public static final String CREATE_CARD_TABLE = "CREATE TABLE "
    + CARD_TABLE + "(" + CARD_ID
    + " INTEGER PRIMARY KEY AUTOINCREMENT, " + CARD_NAME
    + " TEXT )";

public static final String CREATE_CARDINFO_TABLE = "CREATE TABLE "
    + CARD_INFO_TABLE + "(" + CARD_ID2
    + " INTEGER, " + CARD_NAME2
    + " TEXT, " + CARD_WEIGHT
    + " REAL, " + CARD_REPS
    + " INTEGER, "
    + " FOREIGN KEY " + "("+ CARD_ID2 +")"+ " REFERENCES " + CARD_TABLE + "("+CARD_ID+"));";

Inserting into the table

    public void insert(String name) {
    open();
    database.beginTransaction();
    ContentValues newValues = new ContentValues();
    // Assign values for each row.
    newValues.put(DataBaseHelper.CARD_NAME, name);

    // Insert the row into your table
    database.insert(DataBaseHelper.CARD_TABLE, null, newValues);
    database.setTransactionSuccessful();
    database.endTransaction();
    close();
}

public void insert2(long id, String name, double weight, int reps) {
    Log.v("id", id + "");
    open();
    database.beginTransaction();
    ContentValues newValues2 = new ContentValues();
    // Assign values for each row.
    newValues2.put(DataBaseHelper.CARD_ID2, id);
    newValues2.put(DataBaseHelper.CARD_NAME2, name);
    newValues2.put(DataBaseHelper.CARD_WEIGHT, weight);
    newValues2.put(DataBaseHelper.CARD_REPS, reps);

    // Insert the row into your table
    database.insert(DataBaseHelper.CARD_INFO_TABLE, null, newValues2);
    database.setTransactionSuccessful();
    database.endTransaction();
    close();
}

Edit: what if I wanted to delete all of the records that have the id of the primary key? because I could just delete them all and add a new list, as I have a temporary arraylist which shows the items, I can just insert each of them into the database after all of the database items have been deleted.

Upvotes: 0

Views: 78

Answers (1)

injecteer
injecteer

Reputation: 20699

if you have an id of the record you want to delete, then the delete() method is here to help:

database.delete( table, "_id=?", new String[]{ id } );

if you want to delete records based on some other fields:

database.delete( table, "someField=? or anotherField=?", new String[]{ val1, val2 } );

if you have a list of id's, you can delete all the records in one sit:

String ids = concatenateIds(); // should return a string "11,22,33,44"
database.delete( table, "_id in (" + ids + ")", new String[]{} );

Note, that you can pass the positional params of the query only as Strings

Upvotes: 1

Related Questions