How to Delete Records (Data) From a Table in SQL

In the ongoing SQL series, till now, we have discussed creating a table, inserting rows, and selecting rows for the data analysis.

What if you want to delete or remove records from a table after finding it irrelevant in the SELECT statement output? For the corresponding purpose, use the SQL DELETE statement.

Although deleting data from SQL tables can be a delicate process. However, with the right skills and knowledge, you can manage your database with confidence and ease.

This post will provide you with information regarding the SQL DELETE statement, its syntax, use cases, common errors and their fixes, and the best practices to utilize the mentioned statement!

What is SQL DELETE Statement

The SQL DELETE statement is utilized for deleting single or multiple records from a database table. It permits you to selectively remove particular rows based on the added one or more conditions. More specifically, this statement is a powerful tool used for maintaining data integrity.

The syntax of the DELETE statement comprises different components, such as DELETE, FROM, WHERE, etc as follows.

DELETE FROM table_name
WHERE condition;

Here:

• The “DELETE” command statement is utilized for deleting records from the mentioned table.
• “table_name” refers to the name of the desired table.
• The “WHERE” clause is added to define the “condition” based on which the record will be deleted.

How to Use SQL DELETE Statement

The DELETE statement can be utilized in several scenarios, such as:

• For deleting or removing a single row or record from a table.
• For deleting or removing multiple rows or records from a table.
• For deleting or removing all rows or records from a table.

Let’s practically demonstrate each of the mentioned case scenarios.

Delete Specific Row in a SQL Table

Over a period of time, you may have noticed that a particular table is accumulating a large amount of data, some of which can be irrelevant or outdated. In such a scenario, you can remove a particular record or row from the table by utilizing the DELETE statement.

To do so, firstly, view the relevant table data using the SELECT statement.

SELECT * FROM authors;

Then, DELETE the row from the “authors” table WHERE the id equals 5.

DELETE FROM authors WHERE id = 5;

For the verification, again view the table content.

SELECT * FROM authors;

Likewise, you can define the condition as per requirements and remove a specific row from the mentioned table.

DELETE FROM editors WHERE name= 'Harry';

The above-given statement removes the row WHERE the name has been found as “Harry“.

Delete Multiple Rows in a SQL Table

Deleting multiple rows can assist in removing incorrect data and replacing it with authentic or accurate information. Moreover, sometimes, it becomes essential to remove multiple rows at once for protecting confidential or sensitive information from unauthorized access.

In this case, we will utilize the “editors” table. Therefore, we will view its data first with the help of the SELECT statement.

SELECT * FROM editors;

Write out the provided command for deleting the records FROM the “editors” table WHERE “age” is less than 30.

DELETE FROM editors WHERE age < 30;

As you can see, two rows have been removed from the “editors” table.

Delete All Rows in a SQL Tabl

There exist chances that you want to feed new data onto an existing table. For this, it is required to remove all rows from the table first and then add new records. In this regard, the DELETE statement can help you out with the first operation.

For the demonstration, we will delete all of the records from the “new_authors” table, so view its data first.

SELECT * FROM new_authors;

It can be seen that currently, the new_authors table has three rows.

Now, specify the DELETE statement without column names or any WHERE clause for removing all records as follows.

DELETE FROM new_authors;

Then, run the SELECT statement for the verification.

SELECT * FROM new_authors;

The output signifies that the new_authors table is now empty.

Common Errors in SQL DELETE Statements

This section is all about discussion related to the common errors that can annoy you while running the DELETE statement and their relevant fixes.

Deleting All Records in a Table

First of all, it is crucial to specify the column names in the DELETE statement when it is required to delete or remove particular data from the mentioned table. Otherwise, the DELETE statement will delete all data at once.

DELETE FROM authors;

Deleting Data Without Using WHERE Clause

Similar to the first case, double-check the condition before running the DELETE statement. If you have not added the WHERE clause, then all of the data will get removed.

DELETE FROM editors;

Delete Multiple Tables Data without Join Clause

In a scenario, where it is required to delete data from multiple tables, firstly, understand the relationship between tables and then use the most suitable JOIN clause.

Here you can see, we have not added the JOIN clause, so the ERROR 1064 will be shown on the shell stating the syntax error.

DELETE FROM authors, editors WHERE authors.id = 3;

The solution is to add the JOIN clause according to your requirements.

DELETE authors, 
editors 
FROM 
  authors 
  INNER JOIN editors ON authors.id = editors.id 
WHERE 
  authors.id = 3;

The given DELETE statement deletes the rows FROM the “authors” and “editors” table WHERE the “id” has been found as 3.

It can be observed that the record with id 3 has been deleted from both tables.

Best Practices for Using SQL DELETE Statement

Want to implement SQL DELETE statement best practices? Check out the given list.

• Back up the database before performing the DELETE operation on a large scale.
• Utilize the WHERE clause for avoiding the deletion of unintended data.
• Test the DELETE query in a development environment first before implementing it on a production database.
• Avoid using the DELETE statement in a loop.
• Clean up unnecessary or old data for improving database performance.

That’s how you can use the SQL DELETE statement in the best possible way.

Conclusion

The DELETE statement is a crucial tool that you must know for the management of the database and the relevant record. It can be used for removing specific, single, or multiple rows or deleting all records at once from a table.

Moreover, it is important for you to understand the best practices to prevent data loss and other issues.

That’s all from this post, keep following our SQL series to be up-to-date!