Delete Statement in SQL

Delete statement is very important statement in SQL.This article covers delete statement with condition and without condition( with proper syntax and examples)

Author-Nisha Paul

As you can guess from the name, this article is about deleting something from the tables. What to delete? Well, we have to delete the records or rows of data when those data are no longer needed or is relevant to our business needs. It is essential to clear up the memory, which is then utilized for storing new data. We can delete all the records, some based on conditions, and even the entire table. All these operations come with specific business needs or criteria.In this article Delete Statement in SQL is covered.

Deleting records from the tables comes under the data manipulation language, and deleting the whole table comes under the data definition language. This article will look into every possible way of deleting records from the tables.

CONTENT

• DESCRIPTION
• DELETE STATEMENT WITH CONDITION
• DELETE STATEMENT IN SUBQUERY
• DELETE STATEMENT WITHOUT CONDITION
• SUMMARY

DESCRIPTION

Delete statement is easy to learn but is a powerful tool. Be cautious while using the delete statement on the records, as the deleted data cannot be recovered. We will go through the delete statement by considering a real-life scenario. Suppose you are the database manager of the book store and you have to keep records of the new books coming in. Let’s say that the name of the database is book_store and it has got two tables of books and book_stock.

To view the table of books –

This table has five fields: book number, book name, author name, price, and genre. There are ten records for ten different books in it. Thus, this is the table of book details.

To view the table of book_stock –

This table has three fields: book number, book name, and the number of books in stock. The table has got ten records for ten different books as well. This table stores the number of books in stock for every book in the table of books.

Thus, in our table of books, we only have the details for the books, and in the table of book_stock, we have the number of books in stock for every book in the books table. We will perform a delete operation in these two tables in different ways to cover every type of delete in SQL.

Also explore: SQL Tutorial for Beginners

DELETE STATEMENT WITH CONDITION

The syntax for the delete statement is –

It is as simple as that. However, you have to keep in mind that if you do not place a condition in the where clause, then all the records from the table will get deleted, which we will see later. For the part of the condition, it has to be very specific to the need. Otherwise, you will lose data that is irrecoverable.

Considering the example of book_store, let’s say there is a huge discount on the books from genre comedy, and all of the books got sold within a few days. Now you don’t wish to re-stock the same book on comedy in your book store. Hence you have to delete the records for the book with the comedy genre.

Deleting the book of comedy genre from books table –

This will leave us the table as such – 

As in the books table, we had just a single record for a genre comedy. Hence only one row is removed.

We have to delete the record from the book_stock as well. However, in the book_stock table, we are not having the column of the genre. In this case, we will use the book number associated with the book of the comedy genre to delete the record. The book’s number is 10, as shown in the tables above.

Thus we get the table like this –

Hence, we have used conditions using different columns for deleting records. Now suppose the book named ‘In the Woods’ got sold out from the genre of mystery. In this case, we cannot use genre = ‘mystery’ as our condition in the where clause, as it will lead to the deletion of all the records having the genre as a mystery. We want to remove the book ‘In the Wood’ only. Thus, we can do it either by name or by the book number associated with it. That is,

Here we have deleted the record for the same book in two different tables. Try it out to see what you will get. You should see the tables with eight records.

DELETE STATEMENT IN SUBQUERY

A subquery is a query inside the query. We can use the concept of subquery for deleting the records. Suppose we want to delete the love genre but only for those books for which the number of books in stock is 12. For this, we have to join the two tables of books and book_stock, as we have the genre field in books and the field of the number of books in stock in table book_stock. After that, we need to place the specific condition to delete only the desired records.

As per the above table, we can see that after placing the required conditions, books of book numbers 4 and 6 will get deleted. Let’s see how to do it.

 If we apply all the conditions as above, then we will get the table as such –

Thus, our condition is accurate, and we are getting the records we need to remove. Now we have to delete these two records from the books table. We have to write the same query but now replace the SELECT with DELETE, as we are deleting the required records.

This will give us the table of –

Thus, the records of books number 4 and 6 are deleted just as we wanted. Now we need to delete the records from the book stock as well. We will do that using the subquery only, but this time without joining the two tables. Let’s see how to do it –

If we want to see the records which are present in the book_stock but not in the books, then we can do this –

This will give us –

Thus, we have to delete these two records from book_stock. For this, we have to replace the select with delete like this –

This will give us the table like this –

Thus the records for book number 4 and 6 are deleted. This is how you can use a subquery to delete the required records.

DELETE STATEMENT WITHOUT CONDITION

Delete statements without condition should be used cautiously, as it will delete all the records from the table. It is the same as the truncate statement. Let’s use the delete statement on table book_stock and see what happens.

DELETE FROM book_stock;

This will give a table like –

Thus, you will just get the table structure without any record in it, as all the records are deleted. The deleted data is irrecoverable; hence you have to be very sure before its usage. Try it out with the books table as well.

SUMMARY

Delete statements help delete unwanted data or data which is no longer relevant to the business. One should use the delete statement with caution and should be clear on the need for the delete statement. While using a subquery with delete statements, it is a recommendation to see the output you will get on applying the conditions in the subquery first. If satisfied with the subquery results, you can use the delete statement.

SQL also allows the deletion of the entire table when no longer needed. So when you are using the delete statement without condition, all the records will get deleted, but the table structure will remain in your database. To delete the table from the database, you need to use the drop table syntax. It will delete the complete table from the database along with the data in it.

We have understood the delete statement using the example of a book store which is analogous to the real-life use case of the delete statements. Usually, one does not have to use the delete statement regularly, and several ruling techniques in SQL are used to grant operation access to the database users. Thus, if you work at any company at a fresher level, it is highly probable that you won’t have access to deleting operations on the tables sooner.