UPDATE Statement

The UPDATE statement in SQL is used to modify existing records in a table. It allows you to change the values of one or more columns for rows that meet specified criteria.

Syntax

The basic syntax for the UPDATE statement is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Explanation:

UPDATE table_name: Specifies the table whose rows you want to update.
SET column1 = value1, column2 = value2, ...: Specifies the columns to update and the new values they should hold. You can update multiple columns by separating them with commas. The value can be a literal value, an expression, or the result of a subquery.
WHERE condition: This clause is crucial. It specifies which rows should be updated. If the WHERE clause is omitted, all rows in the table will be updated, which is usually not the desired behavior and can lead to data loss.

Examples

1. Updating a Single Column for Specific Rows

Suppose you have a table named Employees with columns EmployeeID, FirstName, LastName, and Salary. You want to increase the salary of the employee with EmployeeID 101 by 10%.

UPDATE Employees
SET Salary = Salary * 1.10
WHERE EmployeeID = 101;

2. Updating Multiple Columns for Specific Rows

Update the email address and department for the employee with EmployeeID 105.

UPDATE Employees
SET Email = 'jane.doe.new@example.com',
    Department = 'Marketing'
WHERE EmployeeID = 105;

3. Updating Based on Another Table (Requires specific SQL dialect support)

In some SQL dialects (like SQL Server or PostgreSQL), you can use joins in the UPDATE statement.

Let's say you have a Sales table and a Products table, and you want to update the StockLevel in the Products table based on sales data.

(Note: Syntax may vary slightly between database systems.)

-- Example for SQL Server
UPDATE p
SET p.StockLevel = p.StockLevel - s.QuantitySold
FROM Products AS p
INNER JOIN Sales AS s
  ON p.ProductID = s.ProductID
WHERE s.SaleDate >= '2023-01-01';

Important Consideration: Always test your UPDATE statements on a development or staging environment before running them on a production database. Use SELECT statements with the same WHERE clause first to verify which rows will be affected.

4. Updating Rows Based on a Condition in a Subquery

Update the Status to 'Inactive' for all employees who haven't logged in for more than a year.

UPDATE Employees
SET Status = 'Inactive'
WHERE LastLoginDate < (CURRENT_DATE - INTERVAL '1 year');

(Note: The syntax for date functions and intervals can vary between SQL dialects.)

Result: If the WHERE clause in the first example matched EmployeeID 101, their salary would be increased. If multiple rows matched the condition in subsequent examples, all those rows would be updated accordingly.

Safety Precautions

Use WHERE Clause: Never omit the WHERE clause unless you intend to update every single row in the table.
Backups: Ensure you have recent backups of your database before performing significant updates.
Transactions: Use transactions to wrap your UPDATE statements. This allows you to roll back changes if something goes wrong.
Test: As mentioned, always test your queries.

Understanding and properly using the UPDATE statement is fundamental for managing and maintaining data integrity in SQL databases.