In this tutorial, we will explore the various methods and techniques that we can use to delete a row or multiple rows from a given database table.
Sample Data Setup
Let us start by creating and setting up a table that we can use for demonstration purposes. Feel free to employ any database that you deem appropriate.
NOTE: The methods that are discussed in this tutorial are destructive and will permanently delete the data from your table. Ensure that you have backups for you database and DO NOT use the methods in a production database.
For our example, let us create a table that can store the blog information. Consider the following example queries:
post_id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(255),
content TEXT,
author_id INT,
created_at TIMESTAMP
);
INSERT
INTO
blog_posts (title,
content,
author_id,
created_at)
VALUES
('Introduction to SQL',
'This is a blog post about SQL.',
101,
'2023-01-15 10:00:00'),
('SQL Joins Explained',
'Learn about different SQL joins.',
102,
'2023-02-20 14:30:00'),
('SQL Indexing Techniques',
'Optimizing database performance with indexes.',
101,
'2023-03-10 09:45:00'),
('SQL Best Practices',
'Tips for writing efficient SQL queries.',
103,
'2023-04-05 16:20:00');
The given queries should create a basic table called “blog_post” and populate it with some data. We use this table to illustrate how to remove the rows from a table.
Method 1: SQL Delete Statement
Let us start with an oldie but goodie. In SQL, we can use the DELETE statement to delete a row or multiple rows from a given table. We do this by specifying the table name and adding the WHERE clause in order to specify the actual row that we wish to remove.
For example, suppose we want to remove the row where the “post_id” is equal to 2. We can run the query as follows:
This should remove the row where the “post_id” is equal to 2.
Method 2: SQL Delete with Subquery
In some cases, you might need to delete the rows based on the conditions from another table. This is where the subqueries come into play.
Suppose we have another table containing the author information. We can delete a post from the “blog_posts” table based on a specific author from the “authors” table as shown in the following:
FROM
blog_posts
WHERE
author_id IN (
SELECT
author_id
FROM
authors
WHERE
author_name = 'Linuxhint);
In this example, we delete all the posts by the author whose name is equal to “linuxhint” by first finding the corresponding “author_id” using an SQL subquery.
Conclusion
In this tutorial, we learned how to delete a row or multiple rows from a given database table using the DELETE statement and an SQL subquery.
