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!
2 Answers
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();
}
2 Comments
J. Arbet
Thanks! And why shouldn't I use string concatenation to build a SQL statement? Like is it very bad if I do it like that?
Andreas
@J.Arbet Because the values (e.g.
John) are likely from an external untrusted source, and may be malicious or simply contain characters that cause SQL issues, e.g. an apostrophe. So, unless you make sure to escape all special characters, you become susceptible to SQL Injection attacks (allowing hackers to steal and/or delete your data), or your SQL will simply fail, e.g. for LastName = 'O'Malley'. Using setString() is safe from those issues, and it also allow the database to better optimize the SQL.Try to use this maybe is the solution of your question:
db.delete(TABLE,
KEY1 + "=? AND " + KEY2 + "=? AND " +
KEY3 + "=? AND " + KEY4 + "=?",
new String[] {param1, param2, param3, param4});
If you use a normal select use this:
db.delete(TABLE,"Column1='value1' and column2 like 'value2'...", null);
1 Comment
J. Arbet
Okay but I use the classic way like "SELECT * FROM table_name" or "DELTE FROM table_name WHERE name='John' AND surname='Doe'". Could you tell me how to make that thing right up there to be like this code?
DELETE From STUDENTS Where Student_Id = 1 AND Student_Name LIKE '%An%'?ORrather thanANDsince he proposes the alternative to do multiple queries instead.