DELETE
SQL

The SQL DELETE Statement Explained

intro

Let’s take a look at the essentials of using the fundamental SQL DELETE operator to remove specific data from datasets.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
PostgreSQL logo POSTGRESQL
THE POSTGRESQL DATABASE

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:

  • Removing obsolete or redundant data: Purge outdated, obsolete, or redundant data from a table. This ensures that there is cleanliness and improved quality within a database.
  • Undoing data entry errors: If incorrect or erroneous data has been entered into a table, SQL DELETE can be used to remove those erroneous records and restore the integrity of the database.
  • Cascading deletes: When a record in one table is deleted, it may be necessary to delete related records in other tables. SQL DELETE with cascading deletes can be used to automatically remove records associated via foreign key constraints.

DELETE in SQL: Statement Syntax

The basic syntax for the DELETE SQL statement is:

Copy
        
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:

Copy
        
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:

Copy
        
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:

  1. DELETE FROM table_name: Specifies the name of the table from which you want to delete rows.
  2. WHERE condition: Defines the optional condition that determines which rows will be deleted. This condition filters the rows that meet the criteria for deletion.
  3. RETURNING (select_list | *): Optional clause that allows you to specify the columns whose values you want to retrieve from the deleted rows. You can either specify a list of column names in the select_list or use * to return all columns.

For example, the statement below deletes rows from a table and returns the values of the id of the deleted rows:

Copy
        
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 (ANDOR) combine multiple conditions for more complex filtering.

Copy
        
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.

Copy
        
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 <=.

Copy
        
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:

Creating the ‘SWEs’ demo table
Creating the SWEs demo table
Copy
        
1 Creating the ‘SWEs’ demo table
Copy
        
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:

Executing the Query in DbVisualizer
Executing the Query in DbVisualizer
Copy
        
1 Executing the Query in DbVisualizer
The ‘SWEs’ table
The SWEs table
Copy
        
1 The ‘SWEs’ table

The following query utilizes the DELETE statement to remove one row with the identifier 7 from the SWEs table:

Copy
        
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:

Deleting One Row from the Table
Deleting One Row from the Table
Copy
        
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:

Copy
        
1 DELETE FROM SWEs 2 WHERE id = 5 3 RETURNING *;

The statement returns the result below:

Deleted Row (id=5) Returned to the Client in DbVisualizer
Deleted Row (id=5) Returned to the Client in DbVisualizer
Copy
        
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:

Copy
        
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:

Copy
        
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.

The altered ‘SWEs’ table
The altered SWEs table
Copy
        
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:

Copy
        
1 DELETE FROM SWEs 2 WHERE LastLoginDate < (NOW() - INTERVAL '4 months');
Removing Inactive SWE Accounts
Removing Inactive SWE Accounts
Copy
        
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:

  • Always specify a proper WHERE clause in your DELETE SQL statement to ensure that only the intended records are deleted. Without it, all records in the table will be deleted, which can lead to loss of data.
  • Before executing a DELETE statement on a production database, test it in a controlled environment, such as a development or staging database. This allows you to verify the correctness of the statement and understand its impact before affecting live data.
  • If you're unsure about the records that will be altered by the DELETE statement, begin by running a SELECT statement with the same conditions. This allows you to preview the results and ensure that you're targeting the correct records before executing the DELETE statement.

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:

Copy
        
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?

  • Be cautious: deleted data is gone forever!
  • Back up your data before running DELETE queries.
  • Understand cascading deletes and foreign keys to maintain data integrity.

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.

Dbvis download link img
About the author
Leslie S. Gyamfi.
Leslie S. Gyamfi
Leslie Gyamfi is a mobile/web app developer with a passion for creating innovative solutions. He is dedicated to delivering high-quality products and technical articles. You can connect with him on LinkedIn
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL Not Equal Operator: Definitive Guide with Examples

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-10-14
title

MySQL DISTINCT Clause Explained: Why, How & When

author Lukas Vileikis tags BEGINNER MySQL SQL 7 min 2024-10-10
title

Counter in MySQL: Counting Rows with Ease

author Lukas Vileikis tags MySQL SQL 8 min 2024-10-03
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

The MySQL RENAME COLUMN Statement: How, What, and Why

author Lukas Vileikis tags MySQL SQL 6 min 2024-09-26
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

Distributing Data in a Database: A Guide to Database Sharding

author Lukas Vileikis tags MySQL OPTIMIZATION SQL 7 min 2024-09-09
title

MariaDB Docker: Server Setup Guide

author Lukas Vileikis tags MySQL SQL 5 min 2024-08-29
title

MySQL Error #1045 Explained: Everything You Need to Know

author Lukas Vileikis tags MySQL SQL 5 min 2024-08-26
title

What Is an SQL Query Builder and How Does It Work?

author Antonello Zanini tags SQL 8 min 2024-08-19

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗