How can a table be deleted from the database? provide an example.

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.

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;

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

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

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”:

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:

• 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:

This will result in the following:

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”:

This will result in the following:

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:

This will result in the following:

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.

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:

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

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:

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:

To check whether all the records have been deleted:

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.