SQLite DELETE

“The delete command in SQLite allows us to delete certain entries according to our needs. When removing rows from a table after an insertion operation, we can use the SQLite delete statement. We can remove specific rows, several rows, or all rows from a table that uses the SQLite delete statement, depending on the user’s needs. The most significant aspect of the delete query is that SQLite does not enable the delete restrict statement. Now, we will discover SQLite delete statements and use SQLite delete statements to delete rows from a table with an example.”

Syntax of the DELETE Statement in SQLite

The basic syntax for a DELETE query with a WHERE clause is as given.

We used a delete statement and specified some attributes to delete values from the table, as you can see in the SQLite Delete statement above. Here, the supplied table name refers to the actual table that exists in the database, and the specified search condition refers to the actual WHERE clause condition that we must apply to the table to delete it. When the WHERE clause is removed, the entire table is deleted.

The DELETE command, in essence, removes data from the table identified by the provided table name. All data from the table is erased if the WHERE condition is not found. Only the entries over which the WHERE clause’s Boolean is valid are eliminated when a WHERE clause is given. Columns with a false or NULL expression are retained.

First, we have created the following table as “mobile” in SQLite. Then, we can use the SQLite delete statement to delete the records from the rows, and we can also delete the table. Inside the table “mobile,” the column names are set as MOBILE_NAME, MOBILE_PRICE, MOBILE_CODE, and STATUS with the different data types.

Now, insert some records against each column in the table “mobile” by using the following queries.

As from SQLite select query, we can see the new entry in the table “mobile.”The following record we have inserted above is shown in the table.

Example 1: Using SQLite DELETE With the Condition

In this example, we have a basic SQLite DELETE query with only one criterion in the WHERE clause. We have used a DELETE term at first to delete the table name “mobile” and then applied the condition in the WHERE clause that deletes the record where MOBILE_ID is equal to “3”.

Now, we are going to check whether the MOBILE_ID having value 3 exists or not from the table “mobile.” We have performed the SELECT statement, and within the SELECT, we have the count operator, which counts the number of rows that exists in the table where MOBILE_ID=3. Upon running the following query, we got zero in the result, which means no such record is present in the table “mobile,” whose MOBILE_ID is equal to 3.

To verify the records in the table “mobile,” we have used the SQLite Select statement. It displays the record which is currently present after the delete operation.

Example 2: Using SQLite DELETE With the AND Operator in the WHERE Clause

With the “WHERE” clause, we can utilize an unlimited amount of “AND” or “OR” operators. In the following example, we are using two conditions with the AND operator in the WHERE clause to delete the record which satisfies these two conditions.

We have a DELETE statement that deletes from the table “mobile” as we have defined the table name. After this, we have a WHERE clause to which we have given two conditions by applying the AND operator. The first condition is to check where MOBILE_NAME is equal to “Samsung A32,” and the MOBILE_PRICE is less than the price “45000.0” and delete that record only.

If we want to check whether the record exists in the table or not, we can follow the below query.

The following mobile table shows the record which is present after the deleted record. As you can see, the record with the MOBILE_NAME equals “Samsung A32,” and the MOBILE_PRICE less than 45000 has been deleted successfully from the table.

Example 3: Using SQLite DELETE With the LIKE Operator

Now, we are using the LIKE operator with the SQLite DELETE statement. The LIKE operator will delete all the records from the table “mobile” with the specific character given to the LIKE operator.

We have a DELETE statement with the defined table name “mobile.” This DELETE statement has the WHERE condition, which uses the LIKE operator, and the LIKE operator has passed the character dash “-.” The LIKE Operator is used over the field STATUS from the table “mobile.”

If you noticed from the above table where we have created the table and show the inserted records. There you can see that we have a field STATUS having non-Approved values. Now, by running the above query that non-Approved values containing rows have been successfully deleted from the table as follows:

Example 4: Using SQLite DELETE With the EXISTS Condition

More complex deletes are also possible. When we want to delete data in one table depending on the values in another. Although we can’t use the SQLite FROM clause to list more than a table when eliminating, we use the SQLite EXISTS keyword instead.

For this, we have created a new table as “salesman” as follows:

We inserted two records in the salesman table by using the following query.

You can see the record rows in the given table.

Now, we have a DELETE statement to which we have specified with the WHERE clause with the EXISTS keyword. Then, we have passed a subquery to the EXISTS clause that selects the entire record from table “salesman,” where the ID from the table is equal to the MOBILE_ID from the table mobile.

The following query shows that no record is present now in the table mobile whose id is matched with the id of the table salesman.

Conclusion

We hope that this post has clarified SQLite delete for you. We studied the basic syntax of SQLite delete and saw several examples of SQLite delete in the preceding article. We also acquired the SQLite delete rules. We discovered how and when to utilize SQLite delete from this post.