EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

SQL DELETE ROW

Home » Data Science » Data Science Tutorials » SQL Tutorial » SQL DELETE ROW

SQL DELETE ROW

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

department

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:

sql delete row2

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.

Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (5,275 ratings)
Course Price

View Course

Related Courses
JDBC Training (6 Courses, 7+ Projects)PHP Training (5 Courses, 3 Project)Windows 10 Training (4 Courses, 4+ Projects)PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

sql delete row3

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:

sql delete row4

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.

SELECT statemen

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:

daily_sales table

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.

sql delete row7

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 –

  1. SQL IN Operator
  2. ROLLUP in SQL
  3. SQL Subquery
  4. MySQL Subquery

SQL Training Program (7 Courses, 8+ Projects)

7 Online Courses

8 Hands-on Projects

73+ Hours

Verifiable Certificate of Completion

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
SQL Tutorial
  • Advanced
    • Aggregate Functions in SQL
    • IF ELSE Statement in SQL
    • SQL CASE Statement
    • SQL While Loop
    • SQL INSTR()
    • What is Procedure in SQL
    • Stored Procedure in SQL?
    • SQL Server Constraints
    • SQL DELETE ROW
    • Column in SQL
    • Table in SQL
    • SQL Virtual Table
    • SQL Merge Two Tables
    • SQL Table Partitioning
    • SQL Temporary Table
    • SQL Clone Table
    • SQL Rename Table
    • SQL LOCK TABLE
    • SQL Mapping
    • Cursors in SQL
    • AND in SQL
    • Wildcard in SQL
    • SQL FETCH NEXT
    • SQL Views
    • Triggers in SQL
    • SQL UPDATE Trigger
    • SQL AFTER UPDATE Trigger
    • SQL Update Statement
    • SQL DROP TRIGGER
    • Views in MySQL
    • SQL COMMIT
    • Distinct Keyword in SQL
    • PARTITION BY in SQL
    • SQL Set Operators
    • SQL UNION ALL
    • Metadata in SQL
    • SQL Bulk Insert
    • Array in SQL
    • SQL REGEXP
    • JSON in SQL
    • SQL For loop
    • EXPLAIN in SQL
    • SQL Cluster
    • SQL Backup
    • SQL Pattern Matching
    • SQL Users
    • ISNULL SQL Server
    • SQL Import CSV
  • Basic
    • What is SQL
    • Careers in SQL
    • Careers in SQL Server
    • IS SQL Microsoft?
    • SQL Management Tools
    • What is SQL Developer
    • Uses of SQL
    • How to Install SQL Server
    • What is SQL Server
    • Database in SQL
    • SQL Data Types
    • SQL Keywords
    • Composite Key in SQL
    • SQL Constraints
    • Transactions in SQL
    • First Normal Form
    • SQL Server Data Types
    • SQL Administration
    • SQL Variables
    • Cheat sheet SQL
  • Operators
    • SQL Operators
    • SQL Arithmetic Operators
    • SQL Logical Operators
    • SQL String Operators
    • Ternary Operator in SQL
  • Commands
    • SQL Commands
    • SQL Alter Command
    • SQL Commands Update
    • SQL DML Commands
    • FETCH in SQL
  • Clause
    • SQL Clauses
    • SQL IN Operator
    • SQL LIKE Clause
    • SQL NOT Operator
    • SQL Minus
    • SQL WHERE Clause
    • SQL with Clause
    • SQL HAVING Clause
    • GROUP BY clause in SQL
    • ORDER BY Clause in SQL
    • SQL ORDER BY CASE
    • SQL ORDER BY DATE
    • SQL ORDER BY Alphabetical
    • SQL ORDER BY Ascending
    • SQL GROUP BY Month
    • SQL GROUP BY Multiple Columns
    • SQL GROUP BY DAY
    • SQL GROUPING SETS
  • Queries
    • SQL Insert Query
    • SQL SELECT Query
    • SQL SELECT RANDOM
    • SQL Except Select
    • SQL Subquery
    • SQL SELECT DISTINCT
    • SQL WITH AS Statement
  • Keys
    • SQL Keys
    • Primary Key in SQL
    • Foreign Key in SQL
    • Unique Key in SQL
    • Alternate Key in SQL
  • Functions
    • SQL Date Function
    • SQL String Functions
    • SQL Compare String
    • SQL Window Functions
    • SQL Timestamp
    • SQL TO_DATE()
    • SQL DATEADD()
    • ANY in SQL
    • LIKE Query in SQL
    • BETWEEN in SQL
    • LTRIM() in SQL
    • TOP in SQL
    • SQL Select Top
    • Merge SQL
    • SQL TRUNCATE()
    • SQL UNION
    • SQL ALL
    • SQL INTERSECT
    • SQL Alias
    • SQL Server Substring
    • CUBE in SQL
    • SQL RANK()
    • SQL CTE
    • SQL LAG()
    • SQL MID
    • SQL avg()
    • SQL WEEK
    • SQL DELETE
    • SQL DATEPART()
    • SQL DECODE()
    • SQL DENSE_RANK()
    • SQL NTILE()
    • SQL NULLIF()
    • SQL Stuff
    • SQL Ceiling
    • SQL EXISTS
    • SQL LEAD()
    • SQL ROW_NUMBER
    • SQL Server Replace
    • T-SQL INSERT
    • SQL Ranking Function
  • Joins
    • Join Query in SQL
    • Types of Joins in SQL
    • Types of Joins in SQL Server
    • SQL Inner Join
    • SQL Join Two Tables
    • SQL Delete Join
    • SQL Left Join
    • SQL Right Join
    • SQL Cross Join
    • SQL Outer Join
    • SQL Full Join
    • SQL Self Join
    • Natural Join SQL
    • SQL Multiple Join
  • Interview Questions
    • SQL Interview Questions
    • Advance SQL Interview Questions
    • SQL Joins Interview Questions
    • SQL Server Interview Questions

Related Courses

JDBC Training Course

PHP course

Windows 10 Training

SQL Course Training

PL/SQL Certification Courses

Oracle Certification Courses

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2020 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

Special Offer - SQL Training Program (7 Courses, 8+ Projects) Learn More