SQL DELETE

Introduction to SQL DELETE

The SQL DELETE statement is used to remove rows from a table in a database. Unlike TRUNCATE, which removes all rows and resets any auto-increment counters, DELETE removes specific rows based on the condition provided. This makes DELETE more flexible but potentially slower if removing large numbers of rows. It is important to use a WHERE clause with DELETE to avoid unintentionally removing all rows.

Basic Syntax of SQL DELETE

The basic syntax of a DELETE statement is as follows:

Example: Deleting Specific Rows

Consider a table named employees with the following columns: id, name, position, and salary. To delete an employee with a specific ID, you can use the DELETE statement as shown below:

This command removes the row from the employees table where the id column has a value of 5.

Deleting Multiple Rows

To delete multiple rows, you can specify a condition that matches multiple records. For example, to delete all employees with a salary below 30000:

Using DELETE Without a WHERE Clause

Using DELETE without a WHERE clause will remove all rows from the table, similar to TRUNCATE. However, unlike TRUNCATE, DELETE operations are logged in the transaction log, which allows for recovery and rollback. Here’s how you would delete all rows:

Use this operation cautiously, as it will remove all data from the table.

Difference Between DELETE and TRUNCATE

• DELETE is a DML (Data Manipulation Language) command and can be used with a WHERE clause to delete specific rows.
• TRUNCATE is a DDL (Data Definition Language) command and removes all rows without logging individual row deletions.
• TRUNCATE resets any auto-increment counters, whereas DELETE does not.
• DELETE can be rolled back if used within a transaction, but TRUNCATE cannot be rolled back in most databases.