SQLite - DELETE Query

SQLite - DELETE Query

The SQLite DELETE query is used to remove existing records from a table. It is one of the most powerful and potentially destructive commands in SQL, so it must be used with care.

You almost always use a WHERE clause with the DELETE query to specify which rows to remove. If you omit the WHERE clause, all records in the table will be deleted!

Syntax

The basic syntax of a DELETE query with a WHERE clause is:

DELETE FROM table_name
WHERE [condition];

The [condition] works exactly as it does in a SELECT statement, allowing you to combine multiple conditions with AND or OR operators to precisely target the records for deletion.

Example: Deleting Specific Records

Let’s use the EMPLOYEES table from previous chapters. First, let’s see its current state:

sqlite> SELECT * FROM EMPLOYEES;

ID  NAME    AGE  DEPARTMENT  SALARY
--  ------  ---  ----------  -------
1   David   27   IT          85000.0
2   Allen   25   HR          15000.0
3   Martin  23   Finance     20000.0
4   Mark    25   IT          65000.0
5   Paul    32   Finance     21000.0
6   Kim     22   HR          45000.0
7   James   24   Marketing   10000.0

Now, let’s delete the employee with an ID of 7.

sqlite> DELETE FROM EMPLOYEES WHERE ID = 7;

After executing the command, the table will have the following records:

sqlite> SELECT * FROM EMPLOYEES;

ID  NAME    AGE  DEPARTMENT  SALARY
--  ------  ---  ----------  -------
1   David   27   IT          85000.0
2   Allen   25   HR          15000.0
3   Martin  23   Finance     20000.0
4   Mark    25   IT          65000.0
5   Paul    32   Finance     21000.0
6   Kim     22   HR          45000.0

Deleting All Records

To delete all records from the EMPLOYEES table, you can execute the DELETE query without a WHERE clause:

-- WARNING: This will remove all data from the table.
sqlite> DELETE FROM EMPLOYEES;

After this operation, a SELECT * FROM EMPLOYEES; will return no rows. Note that this operation does not remove the table itself, only its contents. The table structure remains.

For deleting all rows, this command fires DELETE triggers for each row removed. If you want to quickly clear a table and don’t need triggers to run, a faster (though non-standard) approach is TRUNCATE TABLE, which in SQLite is often emulated with DELETE and a manual reset of the autoincrement counter if one exists.

Professional Best Practices and Safeguards

Given the permanent nature of DELETE, professionals follow strict safeguards:

1. Verify with SELECT First

This is the most important best practice. Before you run a DELETE command, write a SELECT statement with the exact same WHERE clause. This allows you to preview the exact set of rows that will be deleted, preventing catastrophic mistakes.

-- Step 1: Preview the rows to be deleted
sqlite> SELECT * FROM EMPLOYEES WHERE DEPARTMENT = 'HR';

ID  NAME   AGE  DEPARTMENT  SALARY
--  -----  ---  ----------  -------
2   Allen  25   HR          15000.0
6   Kim    22   HR          45000.0

-- Step 2: If the preview is correct, execute the DELETE
sqlite> DELETE FROM EMPLOYEES WHERE DEPARTMENT = 'HR';

2. Use Transactions

When performing risky operations, wrap them in a transaction. A transaction allows you to undo your changes if you make a mistake. You can review the changes before making them permanent with COMMIT or discard them with ROLLBACK.

sqlite> BEGIN TRANSACTION;

-- Perform the deletion
sqlite> DELETE FROM EMPLOYEES WHERE AGE < 25;

-- At this point, you can run SELECT to check the state of the table
sqlite> SELECT * FROM EMPLOYEES;

-- If you made a mistake, you can undo it!
sqlite> ROLLBACK;

-- Or, if everything is correct, make it permanent
sqlite> COMMIT;