How to Delete Row in SQL Server [10 Practical Examples]

This article will guide you through the SQL DELETE statement, exploring its syntax, usage in various scenarios like deleting single or multiple rows, and handling complex deletions with JOINs and subqueries.

 

The SQL DELETE statement is a command used to remove rows from a table in a database. It is an essential tool in SQL for managing and maintaining data integrity by allowing the deletion of unnecessary or obsolete data. The DELETE command is widely used in database operations, particularly in CRUD (Create, Read, Update, Delete) operations, which form the backbone of data handling in most database systems.

 

1. Basic Syntax of the DELETE Command

The fundamental syntax of the SQL DELETE statement is straightforward. The basic form is:

DELETE FROM table_name WHERE condition;

• table_name: This is the name of the table from which you want to delete rows.
• WHERE condition: This specifies which rows should be deleted. If the WHERE clause is omitted, all rows in the table will be deleted.

For example, to delete a row from a table named employees where the employee ID is 123, you would write:

DELETE FROM employees WHERE employee_id = 123;

This command will remove the row from the employees table where the employee_id column matches the value 123.

 

2. Using DELETE with WHERE Clause

The WHERE clause in the SQL DELETE statement is crucial for specifying which rows should be removed from a table. It allows you to define conditions to target specific records for deletion.

Example and Explanation:

Suppose you have a table customers, and you want to delete a customer with a specific ID. The SQL statement would be:

DELETE FROM customers WHERE customer_id = 5;

In this example, only the row where customer_id equals 5 will be deleted. If the WHERE clause is not used or is incorrectly specified, it could lead to the deletion of all rows or unintended rows.

 

3. Deleting Multiple Rows

To delete multiple rows in SQL, you can use various operators in the WHERE clause like IN, BETWEEN, or other conditional expressions.

 

3.1 Using the IN Operator:

The IN operator allows you to specify multiple values in the WHERE clause.

DELETE FROM customers WHERE region IN ('East', 'North');

This command will delete all rows in the customers table where the region is either 'East' or 'North'.

 

3.2 Using the BETWEEN Operator:

The BETWEEN operator is used to delete rows within a certain range.

DELETE FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

This will delete all orders placed in January 2023.

 

3.3 Specifying Conditions:

You can also use conditional expressions to target rows for deletion.

DELETE FROM products WHERE price > 100 AND stock < 50;

This command will delete all products priced above $100 with stock levels below 50 units.

 

4. DELETE vs TRUNCATE

The DELETE and TRUNCATE commands in SQL are used for removing data from tables, but they have significant differences in terms of usage and performance.

 

4.1 DELETE Statement:

• Functionality: DELETE removes rows from a table based on the specified conditions in the WHERE clause. If no WHERE clause is provided, it removes all rows.
• Logging: It logs each row deletion, making it slower but allowing transactions to be rolled back.
• Selective Deletion: It allows selective deletion of rows.
• Triggers: Can activate triggers as each row is processed.

DELETE FROM table_name WHERE condition;

 

4.2 TRUNCATE Command:

• Functionality: TRUNCATE quickly removes all rows from a table, essentially resetting it.
• Logging: It logs the deallocation of the data pages of the table, which makes it faster but not suitable for transaction rollback.
• Selective Deletion: It does not allow conditional removal of rows. It's an all-or-nothing operation.
• Triggers: Does not activate triggers as it does not process each row individually.

TRUNCATE TABLE table_name;

 

5. Deleting Data from Multiple Tables

Deleting data from multiple tables in SQL can be accomplished using JOINs or subqueries, but the approach differs based on the SQL database system being used.

 

5.1 Using JOINs:

In some SQL databases like MySQL, you can use JOINs in a DELETE statement to remove data from multiple related tables.

DELETE t1, t2 FROM table1 t1 
INNER JOIN table2 t2 ON t1.id = t2.foreign_id 
WHERE condition;

This statement will delete rows from both table1 and table2 where the specified condition is met.

 

5.2 Using Subqueries:

In SQL databases where DELETE JOINs are not supported (like Oracle), subqueries are used to achieve the same result.

DELETE FROM table1 WHERE id IN (SELECT foreign_id FROM table2 WHERE condition);

This will delete rows from table1 where the IDs match those found by the subquery in table2.

 

6. Using DELETE with JOINs and Subqueries

In SQL, DELETE statements can be combined with JOINs and subqueries to handle more complex data removal scenarios, especially when dealing with relational data spread across multiple tables.

 

6.1 Using DELETE with JOINs:

In SQL databases that support this feature (like MySQL), you can use JOINs in your DELETE statements to remove data from multiple related tables simultaneously.

DELETE t1, t2
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.foreign_id
WHERE t1.condition = true;

In this example, the DELETE statement removes matching rows from both table1 (aliased as t1) and table2 (aliased as t2) based on a condition.

 

6.2 Using DELETE with Subqueries:

For databases that do not support DELETE with JOINs (like SQL Server or Oracle), subqueries are used.

DELETE FROM table1
WHERE id IN (SELECT foreign_id FROM table2 WHERE condition);

Here, the subquery identifies which rows should be deleted from table1 based on a condition applied in table2.

 

7. Checking Data Before Deletion

Before performing a DELETE operation, especially in complex scenarios, it's crucial to verify which rows will be affected. This precaution helps prevent accidental data loss.

 

7.1 Using a SELECT Statement for Preview:

To preview the data that will be deleted, you can write a SELECT statement with the same conditions as your intended DELETE statement.

SELECT * FROM table_name WHERE condition;

Replace condition with the same condition you plan to use in your DELETE statement. This SELECT statement will display the rows that would be deleted by your DELETE statement, allowing you to verify that you're targeting the correct data.

 

7.2 Using a Transaction for Reversibility:

Another method is to use transactions, particularly in databases that support transactional DDL (Data Definition Language) operations.

BEGIN TRANSACTION;

DELETE FROM table_name WHERE condition;

-- Verify the rows affected, then either commit or rollback
-- COMMIT;
-- ROLLBACK;

Here, you can execute the DELETE statement within a transaction, check the outcome, and decide whether to commit the changes or roll back to undo them.

 

8. Summary

In summary, the SQL DELETE statement is a fundamental command used to remove rows from a database table. Its basic syntax involves specifying the target table and conditions for deletion using a WHERE clause. It's important to use the WHERE clause carefully to avoid unintended data loss. For deleting multiple rows, operators like IN and BETWEEN can be utilized. DELETE can be combined with JOINs and subqueries for complex deletion scenarios across multiple tables. It is crucial to preview affected rows using a SELECT statement before executing DELETE to ensure accuracy. Handling errors such as foreign key constraints, permission issues, and syntax errors is essential for maintaining data integrity. Additionally, understanding the differences between DELETE and TRUNCATE is important for database management, particularly regarding performance implications and use cases.

For further reading and detailed guidance, you can refer to the official documentation of SQL DELETE Statement.