How to delete a row in SQL Server

In SQL Server, there are several ways to delete a row from a table depending on your specific requirements and preferences. Here, I'll explain a few common methods to delete a row from a table stored in SQL Server.

Using the DELETE Statement

The most straightforward and widely used method to delete a row from a table is by using the DELETE statement. The basic syntax is as follows:

DELETE FROM TableName
WHERE condition;

TableName is the name of the table from which you want to delete a row.
condition is a WHERE clause that specifies the conditions for which rows should be deleted. For example, you can use column values to identify the specific row(s) you want to delete.
Here's an example that deletes a row from a hypothetical "Employees" table where the employee with the ID of 101 is to be deleted:

DELETE FROM Employees
WHERE EmployeeID = 101;

Using the TOP clause with the DELETE Statement

If you want to delete a specific number of rows from the table, you can use the TOP clause in conjunction with the DELETE statement. This is useful if you want to limit the number of rows deleted. Here's the syntax:

DELETE TOP (N) FROM TableName
WHERE condition;

N is the number of rows you want to delete.
TableName is the name of the table.
condition is the optional WHERE clause to specify the conditions for deletion.
Example:

DELETE TOP (5) FROM Employees
WHERE Department = 'HR';

This query will delete the top 5 rows from the "Employees" table where the department is 'HR'.

Using a Common Table Expression (CTE)

Another method for deleting rows from a table is by using a Common Table Expression (CTE). This can be especially useful when you want to delete rows based on complex conditions or when you need to delete rows from multiple tables in a single query. The basic syntax is as follows:

WITH CTE AS (
SELECT *
FROM TableName
WHERE condition
)
DELETE FROM CTE;

CTE is the name you give to the common table expression.
TableName is the name of the table.
condition is the condition for selecting rows to delete.
Example:

WITH CTE AS (
SELECT *
FROM Employees
WHERE Salary < 30000
)
DELETE FROM CTE;

In this example, a CTE is used to select employees with a salary less than $30,000, and then the DELETE statement is applied to the CTE, effectively deleting those rows.

Using DELETE JOIN Statement

You can use the DELETE statement with a JOIN clause to delete rows from multiple tables based on a condition. This is useful when you have related tables and want to maintain referential integrity.
Example:

DELETE e
FROM Employees e
JOIN Departments d
ON e.DepartmentID = d.DepartmentID
WHERE d.DepartmentName = 'IT';

This statement deletes employees from the Employees table whose DepartmentName is 'IT' in the Departments table.

Using Subqueries

You can also use a subquery to specify the condition for deleting rows. For example, to delete rows from one table based on a condition in another table:

DELETE FROM Orders
WHERE CustomerID IN
(SELECT CustomerID FROM Customers WHERE Country = 'USA');

In this example, rows from the Orders table are deleted where the CustomerID matches those customers from the Customers table with the Country 'USA'.

Before performing any deletion operation in a production environment, it's important to ensure you have a backup and are absolutely certain about the rows you want to delete, as these operations can be irreversible. Always exercise caution and consider transaction safety when working with DELETE statements in SQL Server.