DELETE - Removing Data from Tables in SQL

The DELETE statement in SQL is used to remove one or more records from a table. This operation is irreversible, so it is important to use it with caution. The DELETE statement can remove data based on specific conditions, which are provided using the WHERE clause. If no WHERE clause is specified, all rows in the table will be deleted.

1. Basic DELETE Statement

The most basic form of the DELETE statement involves specifying the table from which you want to remove data and using the WHERE clause to identify which rows to delete.

Example: Basic DELETE Statement

      -- Delete the student record with StudentID 1
      DELETE FROM Students
      WHERE StudentID = 1;
    

In this example, the DELETE statement removes the record where 'StudentID' is 1 from the 'Students' table. The WHERE clause ensures that only the specific record is deleted, not all rows.

2. Deleting Multiple Rows

You can use the DELETE statement to remove multiple rows by specifying a condition in the WHERE clause that matches more than one record.

Example: Deleting Multiple Rows

      -- Delete all students who are under 20 years old
      DELETE FROM Students
      WHERE Age < 20;
    

In this example, the DELETE statement removes all records from the 'Students' table where the 'Age' is less than 20. The WHERE clause identifies which rows to delete.

3. Deleting All Rows from a Table

If you want to delete all the rows in a table, you can omit the WHERE clause. Be very careful when doing this, as it will remove all data in the table.

Example: Deleting All Rows

      -- Delete all records from the 'Students' table
      DELETE FROM Students;
    

In this example, all records in the 'Students' table are removed because no WHERE clause is specified. The table structure remains intact, but the data is deleted.

4. Using DELETE with JOIN

You can also use the DELETE statement with a JOIN to delete records from one table based on data from another table. This is useful when you need to delete rows that meet certain conditions related to another table.

Example: Using DELETE with JOIN

      -- Delete students who are enrolled in the 'Math' course
      DELETE Students
      FROM Students
      JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
      WHERE Enrollments.CourseName = 'Math';
    

In this example, the DELETE statement removes students from the 'Students' table who are enrolled in the 'Math' course. The JOIN between the 'Students' and 'Enrollments' tables ensures that the deletion is based on the enrollment condition.

5. Using DELETE with Subqueries

A DELETE statement can also use subqueries in the WHERE clause to remove data based on a more complex condition.

Example: Using DELETE with Subqueries

      -- Delete students who are not enrolled in any course
      DELETE FROM Students
      WHERE StudentID NOT IN (SELECT StudentID FROM Enrollments);
    

In this example, the DELETE statement removes students from the 'Students' table who do not have a corresponding record in the 'Enrollments' table. The subquery checks for students who are not enrolled in any course.

6. Deleting Data Using a Condition with LIKE

You can use the LIKE operator in the WHERE clause of the DELETE statement to delete rows that match a specific pattern in a column.

Example: Deleting Data with LIKE

      -- Delete all students whose name starts with 'J'
      DELETE FROM Students
      WHERE Name LIKE 'J%';
    

In this example, the DELETE statement removes all students whose name starts with the letter 'J'. The LIKE operator with the pattern 'J%' matches any name that begins with 'J'.

7. Cascading Deletes

When you delete a record from a table that has related data in other tables (due to foreign key relationships), you may want to delete the related records as well. This is called a cascading delete, and it can be set up using foreign key constraints with the ON DELETE CASCADE option.

Example: Cascading Deletes

      -- If a student is deleted, all related records in the 'Enrollments' table will also be deleted
      ALTER TABLE Enrollments
      ADD CONSTRAINT FK_Student
      FOREIGN KEY (StudentID)
      REFERENCES Students(StudentID)
      ON DELETE CASCADE;
    

In this example, the ON DELETE CASCADE option is used in the foreign key constraint between the 'Students' and 'Enrollments' tables. When a student record is deleted, all related enrollment records in the 'Enrollments' table will be automatically deleted as well.

8. Rollback and Committing Deletes

As with the UPDATE statement, you can use ROLLBACK to undo a DELETE operation if it was executed in a transaction. This ensures that you can reverse the deletion if you make a mistake before committing the changes.

Example: Using ROLLBACK

      -- Start a transaction, delete a student record, and then rollback
      BEGIN TRANSACTION;
      DELETE FROM Students
      WHERE StudentID = 3;
      ROLLBACK;
    

In this example, the student with 'StudentID' 3 is deleted within a transaction. However, the ROLLBACK statement undoes the delete operation, and the record is not permanently removed.

Conclusion

The DELETE statement is a powerful SQL command for removing data from a table. By using conditions in the WHERE clause, you can target specific rows to delete. However, it is crucial to exercise caution when using DELETE, especially when omitting the WHERE clause or performing cascading deletes, as it can result in the permanent loss of data.