SQL - Delete Table

SQL - Deleting Data from a Table

In SQL, managing data includes removing records that are no longer needed. The primary command for this is DELETE, a Data Manipulation Language (DML) statement. It’s crucial to understand that DELETE only removes rows from a table; it does not alter the table’s structure, such as its columns, indexes, or constraints. This tutorial covers how to safely and effectively delete data using the DELETE statement and introduces related commands for different use cases.

Understanding DELETE, TRUNCATE, and DROP

Before we dive into examples, it’s essential to distinguish between three commands that remove things from a database:

• DELETE: A DML command that removes rows from a table. It can remove all rows or a specific subset based on a WHERE clause. Each row deletion is logged, so it can be slow for large tables, but it’s also transactional and can be rolled back.
• TRUNCATE: A Data Definition Language (DDL) command that removes all rows from a table quickly. It’s not as fine-grained as DELETE (no WHERE clause) and is often a minimally logged operation, making it much faster. It also resets identity columns in some database systems.
• DROP: A DDL command that completely removes the entire table, including its structure, data, and indexes. This action is irreversible.

Deleting Specific Rows with the WHERE Clause

The most common use of DELETE is to remove specific rows that match a condition. This is achieved by using the WHERE clause. Omitting the WHERE clause is dangerous, as it will delete all rows in the table.

Syntax

The basic syntax for deleting specific rows is:

DELETE FROM table_name
WHERE condition;

Example

Let’s set up a modern employees table for our examples. Note the use of SERIAL or AUTO_INCREMENT for the primary key, which is common practice.

-- Sample table schema for PostgreSQL/MySQL
CREATE TABLE employees (
    id SERIAL PRIMARY KEY, -- Use AUTO_INCREMENT for MySQL
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary NUMERIC(10, 2),
    is_active BOOLEAN DEFAULT TRUE
);

-- Insert sample data
INSERT INTO employees (first_name, last_name, department, salary, is_active) VALUES
('Ramesh', 'Fadatare', 'Engineering', 75000.00, TRUE),
('Khilan', 'Patel', 'Engineering', 68000.00, TRUE),
('Kaushik', 'Das', 'Marketing', 65000.00, TRUE),
('Chaitali', 'Gupta', 'Sales', 82000.00, FALSE),
('Hardik', 'Pandya', 'Sales', 85000.00, TRUE),
('Komal', 'Sharma', 'HR', 55000.00, TRUE),
('Muffy', 'Moon', 'Marketing', 71000.00, FALSE);

Let’s say Hardik Pandya has left the company. We need to delete his record from the employees table.

DELETE FROM employees
WHERE first_name = 'Hardik' AND last_name = 'Pandya';

Output

Query OK, 1 row affected.

Verification

You can verify the deletion by running a SELECT query. Notice the record with ID 5 is gone.

SELECT * FROM employees;

The resulting table will no longer contain Hardik’s record.

Deleting with Multiple Conditions

You can create more complex deletion criteria using logical operators like AND and OR. This is useful for cleaning up data, for instance, removing all inactive employees from the Sales department.

Syntax

DELETE FROM table_name
WHERE condition1 AND (condition2 OR condition3);

Example

Let’s delete all employees who are marked as inactive (is_active = FALSE). In our sample data, this applies to Chaitali and Muffy.

DELETE FROM employees
WHERE is_active = FALSE;

Output

Query OK, 2 rows affected.

Verification

A SELECT * FROM employees; query will now show that only active employees remain.

Deleting All Rows from a Table

To delete every record in a table, you can use DELETE without a WHERE clause. Use this with extreme caution.

-- This will delete all records from the employees table.
DELETE FROM employees;

For this purpose, TRUNCATE TABLE is often a better choice.

Performance: DELETE vs. TRUNCATE

When deleting all rows, TRUNCATE TABLE employees; is significantly faster than DELETE FROM employees;. This is because DELETE removes rows one by one and logs each deletion, while TRUNCATE deallocates the data pages in a single, minimally logged operation. However, TRUNCATE cannot be easily rolled back and will not fire ON DELETE triggers.

Feature	DELETE	TRUNCATE
Can use WHERE clause	Yes	No
Fires DELETE triggers	Yes	No
Performance	Slower (row-by-row)	Faster (deallocates pages)
Transactional	Yes (can be rolled back)	Partially (cannot be rolled back in some systems)
Resets Identity	No	Yes (in most systems)

Safety First: Best Practices for Deleting Data

Accidental data deletion can be catastrophic. Always follow these best practices:

• Preview Before Deleting: Before running a DELETE statement, write a SELECT statement with the exact same WHERE clause to see which rows will be deleted. SELECT * FROM employees WHERE is_active = FALSE;
• Use Transactions: Wrap your DELETE statement in a transaction. This allows you to review the changes and either COMMIT them permanently or ROLLBACK to undo the deletion if you made a mistake.
• Backup Regularly: Ensure you have a reliable backup and recovery strategy in place. This is your ultimate safety net.
• Use Specific Conditions: Be as specific as possible in your WHERE clause. Using a unique identifier like id is the safest way to target a single row.

Here is an example of using a transaction for a safe deletion:

BEGIN TRANSACTION;

-- The delete statement to be tested
DELETE FROM employees WHERE department = 'HR';

-- At this point, you can run a SELECT to verify the change in this transaction
-- SELECT * FROM employees;

-- If correct, make it permanent
-- COMMIT;

-- If incorrect, undo the change
-- ROLLBACK;