SQLite Delete

Definition on SQLite Delete

SQLite provides the delete command to the user, in which we can delete the specified records as per the user requirement. After insertion operation sometimes we need to remove rows from the table in that case we can use SQLite delete statement. By using the SQLite delete statement we can delete single rows, multiple rows, and all rows from the table as per the user requirement. We can use the WHERE clause with delete statement to select a specific row from the table that we need to delete. The main important thing about delete is that SQLite does not support the delete limit statement.

Syntax:

delete from a specified table where specified search condition;

Explanation:

In above syntax, we use delete statement to delete the specified records or we can say that rows from the specified, here specified table name means actual table name and specified search condition means actual condition with WHERE clause that we need to apply on the table for delete operation. When we omit the WHERE clause, it deletes all rows from the table.

How Delete statement work in SQLite?

Basically, the DELETE command eliminates records from the table recognized by the specified able-name.
On the off chance that the WHERE clause is absent, all records in the table are erased. Assuming a WHERE clause is provided, just those columns for which the WHERE clause Boolean articulation is genuine are erased. Columns for which the articulation is bogus or NULL are held.

Restriction on DELETE statement when we use create trigger

The table-name indicated as a feature of a DELETE statement inside a trigger body should be inadequate. At the end of the day, the schema name. Prefix on the table name isn’t permitted inside triggers. In the event that the table to which the trigger is appended isn’t in the temp information base, at that point DELETE statement inside the trigger body should work on tables inside a similar data set as it. In the event that the table to which the trigger is connected is in the TEMP data set, at that point the unfit name of the table being erased is settled similarly all things considered for a high-level statement (via looking through first the TEMP data set, at that point the principle data set, at that point some other data sets in the request they were joined).

The LIMIT and ORDER BY clause (depicted underneath) are unsupported for DELETE proclamations inside triggers.

The RETURNING clause isn’t upheld for triggers.

Optional LIMIT and ORDER BY clauses

On the off chance that a DELETE statement has a LIMIT condition, the most extreme number of lines that will be erased is found by assessing the going with articulation and projecting it to a number worth. On the off chance that the aftereffect of the assessing the LIMIT condition can’t be losslessly changed over to a number worth, it is a mistake. A negative LIMIT value is deciphered as “no restriction”. Assuming the DELETE statement additionally has an OFFSET clause; it is correspondingly assessed and cast to a number worth. Once more, it is a blunder if the worth can’t be losslessly changed over to a whole number. In the event that there is no OFFSET clause, or the determined whole number worth is negative, the powerful OFFSET value is zero.
Assuming the DELETE statement has an ORDER BY clause; all columns that would be erased without the LIMIT provision are arranged by the ORDER BY. The principal M lines, where M is the worth found by assessing the OFFSET statement articulation, are skipped, and the accompanying N, where N is the estimation of the LIMIT specified expression, are erased. In the event that there are not as many as N lines staying subsequent to considering the OFFSET clause, or assuming the LIMIT proviso assessed to a negative worth, all leftover columns are erased.

Examples

Now let’s see the different examples of SQLite delete as follows.

First, we need to create a new table by using the following statement as follows.

create table emp (emp_id integer primary key, emp_name text not null, emp_dept text not null, emp_salary text not null);

Explanation

In above example, we use create table statement to create a new table name as emp with different attributes such as emp_id with integer data type and primary key constraint, emp_name with text data type and not null constraint, emp_dept with text data type and not null constraint and emp_salary with text data type and not null constraint as shown in above statement. The end result of the above statement as shown in below screenshot.

.table

Now insert some records into the emp table by using insert into statement as follows.

Insert into emp (emp_id, emp_name, emp_dept, emp_salary) values (1, “Johan”, “COMP”,”10200”), (2, “Jay”, “IT”,”20000”), (3, “Sameer”, “COMP”,”15000”), (4, “Janny”, “MECH”,”5000”), (5, “Pooja”, “MECH”,”27000”);
select * from emp;

Explanation

In the above statement, we use insert into statement to insert new records into the emp table. The end result of the above statement as shown in the screenshot below.

Now perform the delete operation as follows.

Suppose we need to remove the first row from the table. At that time we can use the following statement as follows.

delete from emp where emp_id = 1;

Explanation

In the above example, we use a delete statement to remove the first row from the table. The end result of the above statement as shown in the screenshot below.

Suppose we need to delete an employee whose name contains Jay. At that time we can use the following statement.

delete from emp where emp_name like “%Jay%”;

Explanation

In the above example, we use a like clause to remove Jay. The end result of the above statement as shown in the screenshot below.

Sometimes we need to delete tables from the database. At that time we can use the following statement as follows.

delete from emp;

Explanation

By using the above statement we can delete the emp table.

Conclusion

We hope from this article you have understood about SQLite delete. From the above article, we have learned the basic syntax of SQLite delete and we also see different examples of SQLite delete. We also learned the rules of SQLite delete. From this article, we learned how and when we use SQLite delete.

Recommended Articles

This is a guide to SQLite Delete. Here we discuss the definition, syntax, How Delete statement work in SQLite? and examples. You may also have a look at the following articles to learn more –

1. SQL Injection Attack
2. SQL COALESCE
3. SQL pivot
4. SQL Formatter