How to Delete a Single Record from a SQL Table Based on Condition

Delete a Single Record

Write a SQL query to delete a single record from a table based on a specific condition.

Solution:

-- Delete the employee with EmployeeID = 4.
DELETE FROM Employees -- Specify the table to delete from.
WHERE EmployeeID = 4; -- Delete only the employee with ID 4.

Explanation:

1. Purpose of the Query :

1. The goal is to delete a specific employee record from the Employees table.
2. This demonstrates how to use the DELETE statement to remove a single row from a table based on a condition.

2. Key Components :

1. DELETE FROM Employees : Specifies the table from which the record will be deleted.
2. WHERE EmployeeID = 4 : Ensures that only the employee with EmployeeID = 4 is deleted.

3. Why use DELETE? :

1. The DELETE statement is used to remove records from a table, making it essential for maintaining clean and accurate data.
2. For example, if an employee leaves the company, their record can be removed from the database using this statement.

4. Real-World Application :

1. For example, in a company database, you might use this query to delete the record of an employee with EmployeeID = 4 who has resigned or been terminated.

Additional Notes:

• “WHERE” clause is critical to ensure that only the intended record is deleted.
• Scenarios where deleting a single record is appropriate, such as:
• Removing outdated or redundant data (e.g., employees who have left the company).
• Correcting erroneous entries in the database.
• Important Considerations :
• Always include a WHERE clause to avoid unintentionally deleting all rows in the table.
• Verify that the condition in the WHERE clause uniquely identifies the target record.

For more Practice: Solve these Related Problems:

• Write a SQL query to delete a single record from the 'customers' table where the deletion condition includes a subquery comparing aggregated values.
• Write a SQL query to delete a single record from a table by joining it with another table to evaluate a complex deletion condition.
• Write a SQL query to delete a single record that meets multiple complex criteria involving date ranges and numeric thresholds.
• Write a SQL query to delete a single record from a table using a LIMIT clause (or its equivalent) to ensure only one record is removed.
• Write a SQL query to delete a single record within a transaction that rolls back the deletion if a secondary condition is not met.

Go to:

• SQL Data Manipulation Language (DML) Exercises Home ↩
• SQL Exercises Home ↩

PREV : Update Multiple Records.
NEXT : Delete Multiple Records.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.