Delete SQLite Row with where clause with multiple clauses

Question

I've been searching around and not managed to find a solution or working example so here goes.

I've set up an SQLite database which has five columns with different fields which effectively builds a list for the user. There will be multiple values in the list that are the same, save for the ROWID which is set to auto increment.

Neither the user nor the program will know the ROWID once a value has been entered into the database, so I want for the user to be able to remove one row if it contains all four of the other fields.

My code looks like this:

Database
            .delete(DATABASE_TABLE,
                    "KEY_DATE='date' AND KEY_GRADE='style2' AND KEY_STYLE='style' AND KEY_PUMPLEVEL='pumpLevel'",
                    null);

But this does not remove any values. I'm almost certain that the syntax of this command is to blame.

How do I format a where clause for delete command with multiple where clauses?

Solution from below:

ourDatabase.delete(DATABASE_TABLE, "ROWID = (SELECT Max(ROWID) FROM "
            + DATABASE_TABLE + " WHERE " + "date=? AND grade=? AND "
            + "style=? AND pump_level=?)", new String[] { date, grade,
            style, pumpLevel });

Sam · Accepted Answer · 2012-08-28T17:45:20.270

25

I have a feeling that KEY_DATE, KEY_GRADE, etc are you Java variable names not your actual column names. Try changing your code to this:

.delete(DATABASE_TABLE,
        KEY_DATE + "='date' AND " + KEY_GRADE + "='style2' AND " +
        KEY_STYLE + "='style' AND " + KEY_PUMPLEVEL + "='pumpLevel'",
        null);

Also I assume that 'date', 'style', etc are stored in local variables, you should use the whereArgs parameter to project yourself from SQL Injection Attacks:

.delete(DATABASE_TABLE,
        KEY_DATE + "=? AND " + KEY_GRADE + "=? AND " +
        KEY_STYLE + "=? AND " + KEY_PUMPLEVEL + "=?",
        new String[] {date, grade, style, pumpLevel});

(where date = "date", grade = "style2", etc)

Added from comments

To delete the last row use this:

.delete(DATABASE_TABLE,
        "ROWID = (SELECT Max(ROWID) FROM " + DATABASE_TABLE + ")",
        null);

To delete your last matching row try this:

.delete(DATABASE_TABLE,
        "ROWID = (SELECT Max(ROWID) FROM " + DATABASE_TABLE + " WHERE " +
            "KEY_DATE='date' AND KEY_GRADE='style2' AND " +
            "KEY_STYLE='style' AND KEY_PUMPLEVEL='pumpLevel')",
        null);

But see my second note about SQL Injection Attacks to protect your data.

edited Aug 28 '12 at 17:45

answered Aug 28 '12 at 15:33

Sam

86,580
20
181
179

Sorry, I realise I've asked an idiotic question here. The rows are only identified by the auto incrementing ROWID. What I really want to do is delete the most recent ROWID which contains fields that match the values that are passed in as the context. Do you have a suggestion? – Gareth Bowen Aug 28 '12 at 17:07
I'm still not sure on your exact question... But if you have multiple rows with `KEY_DATE = 'date'`, etc and want to delete the last matching row: you can combine my last two suggestions. – Sam Aug 28 '12 at 17:21
It works. Thanks. If it's not too much trouble can you explain how it works as I don't understand how it knows which values to look for. – Gareth Bowen Aug 28 '12 at 17:25
Sure, `SELECT Max(ROWID)` returns the highest value for `ROWID` which is the last row added since you are using an autoincrementing key. So really you have two commands that happen `SELECT...` finds the largest `ROWID` and passes this value to your `.delete(...)` command. Check out [this guide](http://www.w3schools.com/sql/sql_func_max.asp) for a detailed explanation of various SQL functions like Count(), Max(), Min(), etc. – Sam Aug 28 '12 at 17:30
PS Please click the checkmark is the upper-left corner of my answer to mark your question as solved. – Sam Aug 28 '12 at 17:32
Hmm, I've just realised that this code removes the last entry regardless of the contents of the columns. How can I make sure that it only removes a row if the value of the columns match those from the context that gets passed in? – Gareth Bowen Aug 28 '12 at 17:39
I combined the answers for you. See how the `SELECT` query uses the `WHERE` clause to find a `Max(ROWID`? – Sam Aug 28 '12 at 17:46
Thanks Sam, I've finally solved it by amalgamating the information into the following code: `ourDatabase.delete(DATABASE_TABLE, "ROWID = (SELECT Max(ROWID) FROM " + DATABASE_TABLE + " WHERE " + "date=? AND grade=? AND " + "style=? AND pump_level=?)", new String[] { date, grade, style, pumpLevel });` Thanks for your help. – Gareth Bowen Aug 29 '12 at 09:18

score 8 · Answer 2 · edited Aug 31 '17 at 13:33

8

You should use:

Database.delete(DATABASE_TABLE,
                "KEY_DATE=? AND KEY_GRADE = ? AND KEY_STYLE = ? AND KEY_PUMPLEVEL = ?",
                 new String[]{"date", "style2", "style", "pumpLevel"});

which simplifies escaping values.

edited Aug 31 '17 at 13:33

Vishal Yadav

3,642
3
25
42

answered Aug 28 '12 at 15:39

Diego Torres Milano

65,697
9
111
134

This almost works perfectly. For some reason I have to use the String value that I've associated with the key. ie. instead of `KEY_DATE` I'm using `date`. Unfortunately this deletes all the values that match the criteria of date, grade, style and pumplevel. What I want to do is delete just one of these values and leave the rest. Can this code be adapted to do this? – Gareth Bowen Aug 28 '12 at 23:43
You should delete rows from the table using the primary key, which is unique, and thus only one row matches. – Diego Torres Milano Aug 29 '12 at 04:58
Sorry for being dense, but how can I find the rowid for the rows that match the four values I am looking for? – Gareth Bowen Aug 29 '12 at 07:37
The question you should ask yourself is why you are inserting rows that only differ in its rowid (if this is what you are doing). – Diego Torres Milano Aug 29 '12 at 15:13

Delete SQLite Row with where clause with multiple clauses

2 Answers2

Linked