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-28 17:45:20Z

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, 2012 at 17:45

answered Aug 28, 2012 at 15:33

Sam

87k21 gold badges185 silver badges180 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Gareth Bowen Over a year ago

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?

Sam Over a year ago

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.

Gareth Bowen Over a year ago

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.

Sam Over a year ago

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 for a detailed explanation of various SQL functions like Count(), Max(), Min(), etc.

Sam Over a year ago

PS Please click the checkmark is the upper-left corner of my answer to mark your question as solved.

|

Vishal Yadav · Accepted Answer · 2017-08-31 13:33:55Z

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, 2017 at 13:33

Vishal Yadav

3,6893 gold badges28 silver badges43 bronze badges

answered Aug 28, 2012 at 15:39

Diego Torres Milano

69.9k9 gold badges116 silver badges145 bronze badges

4 Comments

Gareth Bowen Over a year ago

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?

Diego Torres Milano Over a year ago

You should delete rows from the table using the primary key, which is unique, and thus only one row matches.

Gareth Bowen Over a year ago

Sorry for being dense, but how can I find the rowid for the rows that match the four values I am looking for?

Diego Torres Milano Over a year ago

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).

Collectives™ on Stack Overflow

Delete SQLite Row with where clause with multiple clauses

2 Answers 2

8 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related