How can you delete multiple rows in SQL Server?

 In SQL Server, you can delete multiple rows from a table using the DELETE statement in various ways depending on your requirements. Here are the most common methods for deleting multiple rows:

1. Delete Rows Based on a Condition (Using WHERE clause)

If you want to delete multiple rows that meet certain criteria, you can use the DELETE statement with a WHERE clause.

DELETE FROM table_name
WHERE condition;

Example:

To delete all rows where the status column is "inactive":

DELETE FROM employees
WHERE status = 'inactive';

This will delete all rows in the employees table where the status column is 'inactive'.

2. Delete Multiple Rows Using IN

You can delete rows where a column value matches any value in a list using the IN keyword.

Example:

To delete rows where the employee_id is 101, 102, or 103:

DELETE FROM employees
WHERE employee_id IN (101, 102, 103);

3. Delete Rows Using a Subquery

You can delete rows based on the results of a subquery. This is useful when you want to delete records that are related to another table.

Example:

To delete employees who have not been assigned any project:

DELETE FROM employees
WHERE employee_id IN (SELECT employee_id FROM projects WHERE project_id IS NULL);

4. Delete All Rows (No Condition)

If you want to delete all rows in a table (but not the table itself), you can omit the WHERE clause. Note that this will remove all data in the table.

DELETE FROM table_name;

Example:

DELETE FROM employees;

5. Delete Rows Using JOIN

You can also delete rows based on a join with another table. In SQL Server, you can use a DELETE statement combined with a JOIN to delete rows that meet certain conditions in the related table.

Example:

To delete employees who belong to a department that has been marked as 'inactive':

DELETE e
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.status = 'inactive';

This will delete all employees whose department has the status 'inactive'.

6. Delete Top N Rows

If you want to delete a specific number of rows, you can use the TOP keyword in your DELETE statement.

Example:

To delete the top 10 employees by employee_id:

DELETE TOP (10) FROM employees
ORDER BY employee_id;

This will delete the first 10 employees based on the order of their employee_id.

---

Key Considerations:

• Transaction Control: Always ensure you have proper backups and/or use transactions when performing delete operations, especially if deleting a large number of rows.

  For example:

  BEGIN TRANSACTION;
  DELETE FROM employees WHERE status = 'inactive';
  COMMIT;
  

• Performance: Deleting many rows in large tables can be resource-intensive. If you need to delete a huge number of rows, consider breaking it into smaller batches.

  WHILE (1=1)
  BEGIN
    DELETE TOP (1000) FROM employees WHERE status = 'inactive';
    IF @@ROWCOUNT = 0 BREAK;
  END
  

• Referential Integrity: Be aware of foreign key constraints, as they might prevent deletes from succeeding if there are dependent rows in other tables.

7. TRUNCATE (If Appropriate)

If you want to delete all rows from a table and reset any identity columns (if present), you can use TRUNCATE instead of DELETE.

TRUNCATE TABLE employees;

• TRUNCATE is faster than DELETE because it doesn't log individual row deletions, but it also cannot be rolled back in the same way as DELETE if the transaction is not managed properly.

---

Summary:

• Use DELETE with a WHERE clause to delete specific rows.
• Use IN or a subquery to delete rows that match a list of values or another table's result.
• Use JOIN if you need to delete rows based on relationships between tables.
• Use TRUNCATE to delete all rows in a table quickly, but be aware it doesn't allow for selective row deletion or rolling back individual deletions after the fact.