SQL - Update Query

SQL UPDATE Query

The SQL UPDATE statement is used to modify existing records in a table. It is a powerful Data Manipulation Language (DML) command that can change data in a single row, multiple rows, or all rows, based on conditions you specify.

Warning: The UPDATE statement can permanently alter your data. Always be cautious and use the WHERE clause to limit the scope of your changes. Forgetting the WHERE clause will update every row in the table!

Basic UPDATE Syntax

The fundamental structure of an UPDATE statement involves specifying the table, the columns to change, their new values, and the condition to identify which rows to update.

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

Example: Updating a Single Record

Consider a Products table. Let’s update the price of a specific product identified by its ProductID.

/* First, let's see the current state of the product */
SELECT ProductName, Price FROM Products WHERE ProductID = 101;

-- Assume the initial price is 19.99

UPDATE Products
SET Price = 24.99
WHERE ProductID = 101;

This query will only affect the row where ProductID is 101.

Advanced UPDATE Scenarios

Updating Multiple Rows

You can update multiple rows by providing a WHERE clause that matches several records. For instance, let’s apply a 10% discount to all products in a specific category.

UPDATE Products
SET Price = Price * 0.90
WHERE Category = 'Electronics';

Updating from Another Table (UPDATE with JOIN)

A common requirement is to update a table based on values from another table. The syntax for this varies by SQL dialect.

PostgreSQL Syntax:

-- Update the stock count in the 'products' table using information from a 'new_stock_levels' table.
UPDATE products p
SET stock_quantity = s.new_quantity
FROM new_stock_levels s
WHERE p.product_id = s.product_id;

SQL Server / MySQL Syntax:

-- Same goal, but using a JOIN syntax in the UPDATE statement.
UPDATE products p
JOIN new_stock_levels s ON p.product_id = s.product_id
SET p.stock_quantity = s.new_quantity;

Best Practices and Safety Measures

• Use Transactions: Wrap your UPDATE statement in a transaction. This allows you to preview the changes and ROLLBACK if they are incorrect, or COMMIT if they are correct.
• SELECT Before You UPDATE: Before running a complex UPDATE, write a SELECT statement with the exact same WHERE clause. This will show you precisely which rows will be affected.
• Be Specific: When updating a single row, always use the primary key in your WHERE clause to ensure you are not accidentally updating other rows.
• Check for Performance: Updating a large number of rows can lock the table and impact application performance. Consider performing large updates during off-peak hours.

Example of a Safe Update with a Transaction

BEGIN TRANSACTION;

-- The UPDATE statement we intend to run
UPDATE Employees
SET Salary = Salary * 1.05
WHERE DepartmentID = 4;

-- Check the result before committing
SELECT EmployeeID, Salary FROM Employees WHERE DepartmentID = 4;

-- If everything looks correct, commit the changes.
-- COMMIT;

-- If something is wrong, roll back to the original state.
-- ROLLBACK;