intro
Let’s take a look at the essentials of using the fundamental SQL DELETE operator to remove specific data from datasets.
Not all data on a database is good, and you need the ability to remove records you no longer need. This is where the SQL DELETE statement comes in!
In this article, you will learn what the DELETE SQL statement is, how to use it, and explore some best practices.
Let’s dive in!
Introduction to the DELETE Statement in SQL
In SQL, DELETE is the statement used to remove records from a database table. It allows for targeted deletion based on specific criteria or the complete removal of all data from a table.
The DELETE SQL statement acts as an important tool in removing redundant records. Here are some common scenarios where it comes in handy:
DELETE in SQL: Statement Syntax
The basic syntax for the DELETE SQL statement is:
1
DELETE FROM table_name;
This statement deletes all rows from the table called table_name. However, it's often necessary to delete only specific rows that meet certain conditions. This is achieved using the WHERE clause like this:
1
DELETE FROM table_name
2
WHERE condition;
The WHERE clause is optional. When omitted, the DELETE query removes all rows in the table. It filters the rows to be deleted based on the specified condition.
In the next section, you will explore different filtering options. To return the deleted row(s) to the client, you use the RETURNING clause as shown:
1
DELETE FROM table_name
2
WHERE condition
3
RETURNING (select_list | *)
The RETURNING clause in a DELETE statement in SQL allows you to retrieve the rows that were affected by the DELETE operation. When executed, it removes one or more rows from the specified table based on the condition provided in the WHERE clause.
If you also include the RETURNING clause, the database engine not only deletes the rows but also returns the values of the columns specified in the select_list (or all columns using *) for the rows that were deleted.
Breaking down each line of the syntax:
For example, the statement below deletes rows from a table and returns the values of the id of the deleted rows:
1
DELETE FROM table_name
2
WHERE condition
3
RETURNING id;
Filtering Rows for Deletion Through a WHERE Clause
The WHERE clause allows for precise control over which rows are deleted. Here are some common filtering techniques:
Logical operators: Logical operators (AND, OR) combine multiple conditions for more complex filtering.
1
DELETE FROM orders
2
WHERE order_date > '2023-12-31' AND order_status = 'cancelled';
3
-- deletes canceled orders placed after December 31st, 2023
NULL values: The NULL value represents missing data. You can use comparison operators to filter for rows with specific NULL values.
1
DELETE FROM products
2
WHERE product_description IS NULL;
3
-- deletes products with no description
Comparison operators: These operators compare column values to a specific value or another column. Examples include =, <> (not equal), >, <, >=, and <=.
1
DELETE FROM customers
2
WHERE customer_id = 10;
3
-- deletes the customer with ID 10
SQL DELETE Real-World Examples
Time to explore some examples of DELETE in SQL.
Example #1: Deleting row(s) from the table
Set up a demo table by running the query below:
1
Creating the ‘SWEs’ demo table
1
CREATE TABLE SWEs (
2
id SERIAL PRIMARY KEY,
3
title VARCHAR(255) NOT NULL,
4
seniority BOOLEAN NOT NULL DEFAULT false
5
);
6
7
INSERT INTO SWEs (title, senior) VALUES
8
('Frontend', true),
9
('Frontend', false),
10
('DevOps', true),
11
('DevOps', false),
12
('Cloud', true),
13
('Frontend', false),
14
('Backend(Java)', true),
15
('Backend(MEN)', false),
16
('Fullstack(React|Rails)', true),
17
('QA', false);
Executing the query above in DbVisualizer:
1
Executing the Query in DbVisualizer
1
The ‘SWEs’ table
The following query utilizes the DELETE statement to remove one row with the identifier 7 from the SWEs table:
1
DELETE from SWEs
2
WHERE id = 7;
The SQL statement above returns the result below, indicating that the row with id = 7 has been deleted:
1
Deleting One Row from the Table
Now, employ the SQL DELETE statement to eliminate the row with the id of 5 and return the deleted row to the client:
1
DELETE FROM SWEs
2
WHERE id = 5
3
RETURNING *;
The statement returns the result below:
1
Deleted Row (id=5) Returned to the Client in DbVisualizer
Example #2: Removing Inactive User Accounts
Consider you want to remove user accounts that have been inactive for more than 2 months. Before that, add a LastLoginDate column to our already existing SWEs table by running this query in DbVisualizer:
1
ALTER TABLE SWEs
2
ADD LastLoginDate date;
Next, you want to set the LastLoginDate column to multiple and different values for a specified range of the ids. Here is how you can do it:
1
UPDATE SWEs
2
SET LastLoginDate = CASE
3
WHEN id BETWEEN 1 AND 5 THEN '2023-10-01'
4
WHEN id BETWEEN 8 AND 10 THEN '2023-11-09'
5
ELSE LastLoginDate
6
END
7
WHERE id BETWEEN 1 AND 10;
The [CASE statement](https://www.dbvis.com/thetable/postgresql-case-a-comprehensive-guide/) here is used to conditionally set the value of the LastLoginDate column based on the id values. The WHEN clauses specify the range of id values, and the corresponding LastLoginDate values are assigned. The ELSE clause ensures that the last login date remains unchanged for rows outside the specified range. Feel free to insert any LoginDateValue for id = 6 as you want to get rid of null LastLoginDate values.
1
The altered ‘SWEs’ table
Let’s build a query that will remove SWE user accounts that have been inactive for more than 4 months:
1
DELETE FROM SWEs
2
WHERE LastLoginDate < (NOW() - INTERVAL '4 months');
1
Removing Inactive SWE Accounts
In this example, the DELETE statement removes the user account for id = 6 since the LastLoginDate is older than 4 months.
Best Practices and Tips for Using the SQL DELETE Statement
Below are some of the best practices that should be taken into consideration when working with DELETE in SQL:
It’s a wrap!
Conclusion
The SQL DELETE statement offers a precise tool for refining data sets by removing specific entries. To better appreciate its capabilities, you need a tool that helps you manage databases and visually explore query results. This is where a full-featured database client like DbVisualizer comes in. In addition to being able to connect to several DBMSs, it offers advanced query optimization functionality, and full support for all database features, including DELETE operations. Download DbVisualizer for free now!
Frequently Asked Questions
What is DELETE in SQL?
The DELETE SQL statement is a fundamental function in SQL used to delete records from a table in a database. It enables you for targeted deletion based on specific criteria or the complete removal of all data from a table.
How do I delete specific records from a table?
Use the SQL DELETE statement with a WHERE clause to specify the conditions for selecting the records to be deleted like this:
1
DELETE FROM table_name
2
WHERE condition;
How do I delete all rows from a table?
Use DELETE FROM table_name;. However, use this with caution as it's permanent!
How can I delete specific rows?
Use the WHERE clause with conditions to filter the rows for deletion. For example: DELETE FROM customers WHERE customer_id = 10; deletes the customer with ID 10.
What should I consider when using DELETE?
Is it possible to delete records from multiple tables in a single DELETE statement?
No, the SQL DELETE statement can only delete records from a single table at a time. To delete records from multiple tables, you would need to execute separate DELETE statements for each table.
