Introduction to SQL DELETE ROW
DELETE statement in standard query language (SQL) is used to remove one or more rows from the database table. It is a Data Manipulation Language(DML) statement. That means it does not delete data records permanently. We can always perform a rollback operation to undo a DELETE transaction. With DELETE statements we can use WHERE clause for filtering specific rows. There are a few other statements like DROP and TRUNCATE table in SQL which are also used to remove data from the database.
Syntax
Let us now discuss the syntax and parameters used for deleting rows in SQL:
DELETE FROM table_name WHERE condition;
Parameters
The parameters used in this syntax are as follows :
Table_name: This is the name of the table from which you want to delete one or more rows.
Condition: It is a conditional expression used in the WHERE clause to filter one or more rows for removal.
Differences between DELETE TABLE, TRUNCATE TABLE statement and DROP TABLE statements
let us see differences:
| DELETE TABLE | TRUNCATE TABLE | DROP TABLE |
| DELETE statement that removes one or more data rows from the table. | The SQL statement that removes all the data rows from the table. | The SQL statement that removes all the records in the table and also destroys the table at the same time. |
| It is a Data Manipulation Language(DML) statement. | It is a Data Definition Language(DDL) statement. | It is a Data Definition Language(DDL) statement. |
| Since it’s a DML command, we can use a WHERE clause. | We cannot use a WHERE clause. | We cannot use a WHERE clause. |
| We can perform a rollback operation to undo a DELETE table statement. | We cannot perform a rollback operation to undo a TRUNCATE table statement. | We cannot perform a rollback operation to undo a DROP table statement. |
| Table rows are not deleted permanently. | Table rows are deleted permanently. | Table and all its relationships, index, privileges, etc are deleted permanently. |
Examples to Implement SQL DELETE ROW
Having discussed the syntax and parameters used in the DELETE statement, let’s go ahead and try some examples to understand the concept in great detail.
In order to understand the fore come thing examples, consider the following dummy table called “daily_sales”. This table contains details such as products sold, a salesperson who sold it, date of sale, amount, etc. pertaining to daily sales in a departmental store. For illustration purposes, look at the image below.
Code:
SELECT * FROM public.daily_sales
Output:
Example #1
Suppose departmental store’s Delhi (DL) store has been closed permanently and we have to remove all the records pertaining to it from our daily sales table.
Code:
DELETE FROM daily_sales
WHERE store_state = 'DL';
Output:
Explanation: In order to remove details of the Delhi store, we have used a WHERE clause in our DELETE statement to filter rows where store_state is DL. As we can see in the image above, the SQL query we just wrote has successfully removed all rows where store_state is DL.
For the curious ones who are wondering if we have deleted the correct rows or not, we can check it using a simple SELECT statement.
4.5 (8,906 ratings)
View Course
It can be clearly seen that all rows pertaining to the DL store have been removed successfully. Now we have entries only form MH and KA in the daily_sales table.
Example #2
Suppose all the sale_amount entries ranging from 500 to 1500 are entered erroneously by a data entry specialist and we have been tasked to remove all such rows from the daily_sales table.
Code:
DELETE FROM daily_sales
WHERE sale_amount BETWEEN 500 AND 1500;
Output:
Explanation: We have successfully deleted all rows that had any erroneous entry, i.e. sale_amount between 500 to 1500. Next, we can check for ourselves, if the required rows have been deleted from the daily_sales table. For uninitiated, we can use a SELECT statement to fetch all records in the table.
We can see that all rows pertaining to sale_amount between 500 and 1500 have been removed from the table.
Example #3
Suppose all the entries made on a day on which ‘Raj Kini’ made sales are wrong and we have been tasked to remove all such rows from the daily_sales table.
Code:
DELETE FROM daily_sales
WHERE sale_date IN (SELECT sale_date
FROM daily_sales
WHERE salesperson = 'Raj Kini');
Output:
Explanation: This example has been purposely written to illustrate the use of subqueries as a part of the WHERE clause in DELETE statements. And of course, the query must have done its take to remove all the desired rows from the table.
Now, you may pat on your back that you have learned a new statement in SQL to remove desired rows.
Conclusion
SQL DELETE command is a data manipulation language(DML) command which is used to remove one or more rows from a database table. It removes rows temporally and hence the statement can be rolled back.
Recommended Articles
This is a guide to SQL DELETE ROW. Here we discuss an introduction to SQL DELETE ROW, syntax, differences and respective examples. You can also go through our other related articles to learn more –
