How to DELETE using multiple WHERE clauses [SQLITE]

Question

First question I would like to ask is, it is faster to make one delete query with multiple where clauses, or is it faster to do one by one? And second if so then how to make delete query with multiple where clauses? Thanks in advance!

Answer 1

You can't have more than one WHERE clause, but you can make the condition more complex.

If your table has FirstName and LastName, and you want to delete John Doe, Toby Steed, and Linda Powers, then combine AND and OR conditions like this:

DELETE FROM MyTable
 WHERE ( FirstName = 'John' AND LastName = 'Doe' )
    OR ( FirstName = 'Toby' AND LastName = 'Steed' )
    OR ( FirstName = 'Linda' AND LastName = 'Powers' )

Now, if you do it from Java, you shouldn't (EVER!) use string concatenation to build a SQL statement like that. Use a PreparedStatement:

String sql = "DELETE FROM MyTable" +
             " WHERE ( FirstName = ? AND LastName = ? )" +
                " OR ( FirstName = ? AND LastName = ? )" +
                " OR ( FirstName = ? AND LastName = ? )";
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
    stmt.setString(1, "John");
    stmt.setString(2, "Doe");
    stmt.setString(3, "Toby");
    stmt.setString(4, "Steed");
    stmt.setString(5, "Linda");
    stmt.setString(6, "Powers");
    stmt.executeUpdate();
}