How to Delete Records Using Delete in SQL

Data management can be a challenge given the massive amounts of data to deal with. Anyone who manages data knows that sometimes, we need to delete some data from our database, due to changes in the real-world. For example, when a store stops selling an item, its record is removed from the inventory database. This is done with the help of the SQL Delete command. This command is a significant and integral part of this query language.

In this article about the SQL delete command, we’ll be covering the following topics:

  • What Is Delete in SQL?
  • The Syntax for Using the SQL Delete Command
  • Deleting Specific Rows Based on a Single Condition
  • Deleting Specific Rows Based on Multiple Conditions
  • Deleting All the Records From a Table

What Is Delete in SQL?

The Delete command in SQL is a part of the Data Manipulation Language, a sub-language of SQL that allows modification of data in databases. This command is used to delete existing records from a table. Using this, you can either delete specific records based on a condition or all the records from a table.  

NOTE: It is crucial to be entirely sure about using this command, as it deletes the data permanently.

To start using the Delete command, we need to know its syntax.

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
Post Graduate Program in Business Analysis

The Syntax for Using the SQL Delete Command

DELETE FROM table_name

WHERE [condition];

  • The table from which we want to delete rows is specified in the table_name parameter of the DELETE FROM statement.
  • There is an optional WHERE clause in which we can specify the condition according to which the rows should get deleted.

NOTE: Extra caution needs to be taken with the WHERE clause as, without it, all the rows from the table will get deleted.

Multiple conditions can be specified in the WHERE clause with the help of AND and OR operators.

Let’s apply the concepts stated above to the rows of a table.

Deleting Specific Rows Based on a Single Condition

Let’s take a sample “Employee_details” table;

Employee_details_table

  • From the table depicted above, to delete the employee’s record with “EmployeeID” equal to one, we’ll use the following query:

delete_single_row-SQL_Delete.

  • To check the result of the query in the table above, we’ll use the ‘SELECT *’ command:

delete_single_row_output

As we can see, one record has been deleted based on the condition specified in the WHERE clause.

  • From the table above, we need to delete the record of the employee with the name “Arun”:

delete_name

As we can see, “Name” is of character string data type, so the value being specified has to be enclosed in single inverted commas, without which the system will return a syntax error.

The query above will result in the following:

delete_name_output.

  • We can also delete multiple rows using a single query.

From the table displayed above, we need to delete all the employee records with salary less than 60000:

delete_multiple_rows-SQL_Delete

This will result in the following:

delete_multiple_rows_output

As we can see, four rows have been deleted.

More than one condition can be applied to the columns using a single query. Let’s see how that is done.

Deleting Specific Rows Based on Multiple Conditions

We can use AND and OR operators to combine multiple conditions in the WHERE clause of a DELETE command.

  • When the OR operator is used, all the rows that satisfy either one of the specified conditions, are deleted.

From our sample “Employee_details” table, to delete all employee records where the employee name is “Ajay” or the city is “Chennai”:

delete_with_or-SQL_Delete

This will result in the following:

delete_with_or_output.

As we can see, both the rows satisfying either of the conditions have been deleted.

  • When we use the AND operator, only the rows satisfying both the conditions being specified are deleted.

From the above table, we need to delete all the records for employees that belong to “Bangalore” and have a salary less than 50000:

delete_with_and-SQL_Delete

This will result in the following:

delete_with_and_output.

As we can see, only the one record that satisfies both the conditions has been deleted.

Let’s see what happens when we don’t use the WHERE clause.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

Deleting All the Records From a Table

It’s imperative to be extremely careful while using the DELETE command on a table as the data gets deleted permanently. 

  • Without a WHERE clause, all the records of the table are deleted. If not done intentionally, this could cause a lot of problems.

To delete all the records from our “Employee_details” table, we’ll use the following query:

delete_all_rows-SQL_Delete

To check whether all the records are deleted, we’ll use the SELECT command:

delete_all_rows_output

As we can see, the SELECT command returns an empty set as all the records from the “Employee_details” table have been deleted.

  • We can also use the TRUNCATE command to delete all the records from a table. 

This is a part of the Data Definition Language, a sub-language of SQL that allows the creation and modification of database objects. The syntax of this command is:

TRUNCATE table_name;

As we can see, there is no ‘WHERE’ clause, so this command is used only when we need to empty the contents of a table.

To delete all the records from the “Employee_details” table:

truncate-SQL_delete

To check whether all the records have been deleted:

truncate_output

As we can see, the records have been deleted and the table returns an empty set.

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

Next Steps

Deleting records from tables and databases is a huge part of managing data. Sometimes, we insert the wrong data, or the data in a table becomes outdated and needs modification, all of this requires the help of Delete in SQL.

Check out our tutorials to learn how to delete duplicate rows in tables and many SQL statements.

Now that you know how to delete existing records, it is time for you to start learning about other SQL commands so that you can start manipulating and querying data and move forward in your journey to become an expert in SQL. If you liked this article, you must check out our Business Analyst Master’s Program as it covers the A-Z of SQL as well.

Do you have any questions for us? Mention them in the comment section of our “Delete in SQL” article, and we’ll have our experts in the field answer them for you.

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.