Section 3: Updating and Deleting Data

In this section, we'll dive into the SQL statements for updating and deleting data in a database. These operations are essential for maintaining the accuracy and integrity of your database.

Updating Records with UPDATE

The UPDATE statement allows you to modify existing data within a table.

Modifying Existing Data Using the UPDATE Statement

Let's consider an example where we update an employee's last name:

-- Update the last name of employee with ID 1

UPDATE employees

SET last_name = 'Johnson'

WHERE employee_id = 1;

This query modifies the last_name of the employee with ID 1 to 'Johnson'.

Using the SET Clause for Updates

The SET clause specifies the columns to be updated and their new values:

-- Update both first name and last name for employee with ID 2

UPDATE employees

SET first_name = 'Alice', last_name = 'Anderson'

WHERE employee_id = 2;

Here, the first_name and last_name for the employee with ID 2 are updated simultaneously.

Deleting Records with DELETE

The DELETE statement is used to remove records from a table.

Removing Records from a Table Using the DELETE Statement

Let's say we want to delete an employee with ID 3:

-- Delete the employee with ID 3

DELETE FROM employees

WHERE employee_id = 3;

This query removes the employee with ID 3 from the employees table.

Using the WHERE Clause for Targeted Deletions

The WHERE clause is crucial for avoiding unintentional data deletions. It allows you to specify conditions for targeted deletions:

-- Delete all employees hired before 2010

DELETE FROM employees

WHERE hire_date < '2010-01-01';

This query deletes all records from the employees table where the hire_date is before January 1, 2010.

These SQL statements—UPDATE for modifying existing data and DELETE for removing records—are essential for maintaining the accuracy and relevance of your database. Always exercise caution, especially when using the DELETE statement, and double-check your conditions to avoid unintentional data loss.