Updating and Deleting Data

The UPDATE and DELETE statements are responsible for maintaining the accuracy and current status of the data within the database. Because these commands can permanently change or remove data, they are considered the most dangerous DML operations if not executed carefully.

1. Updating Records (UPDATE)

The UPDATE statement is used to modify existing data in one or more records in a table. It must always specify which columns to change and, crucially, which rows to target.

Syntax:

UPDATE table_name
SET column1 = new_value1, column2 = new_value2, ...
WHERE condition;

Execution Example 1: Updating a Single Record

To give the employee with employee_id = 2 a raise, we use the WHERE clause to target that specific primary key.

USE HR_Management;

UPDATE Employees
SET salary = 80000.00
WHERE employee_id = 2;

Execution Example 2: Updating Multiple Records

To update all employees hired after a certain date to have a new default status value.

UPDATE Employees
SET status = 'Active', last_review_date = '2025-10-01'
WHERE hire_date > '2024-01-01';

Warning: If you execute an UPDATE statement without a WHERE clause, it will update every single row in the table, setting the specified column values to the new value for the entire table.

2. Deleting Records (DELETE FROM)

The DELETE FROM statement is used to remove one or more rows entirely from a table.

Syntax:

DELETE FROM table_name
WHERE condition;

Execution Example 1: Deleting a Specific Record

To remove the record for the employee named ‘John Doe’, we target his unique identifier.

-- Assuming John Doe is employee_id = 1
DELETE FROM Employees
WHERE employee_id = 1;

Execution Example 2: Deleting Multiple Records

To remove all records where the status is ‘Terminated’.

DELETE FROM Employees
WHERE status = 'Terminated';

3. The Dangers of Missing WHERE Clauses

Executing DELETE FROM table_name; without a WHERE clause will permanently remove ALL rows from the table.

Best Practice: Always run a SELECT * FROM table_name WHERE condition; first, using the exact same WHERE clause you intend to use in the UPDATE or DELETE statement, to verify the exact set of rows that will be affected.

4. TRUNCATE TABLE (DDL Alternative)

While DELETE FROM table_name; removes all rows via DML, the TRUNCATE TABLE command is a DDL command that also removes all records.

• TRUNCATE TABLE table_name;

Key Differences:

• Speed: TRUNCATE is significantly faster than DELETE for removing all rows because it removes and re-creates the table structure, ignoring row-by-row logging.
• Auto-Increment: TRUNCATE resets the AUTO_INCREMENT counter back to zero (or 1). DELETE does not reset the counter.