Chapter 12: Updating Data with UPDATE

The data in your database isn’t static. Users change their profiles, products go on sale, stock quantities fluctuate, and order statuses need to be updated. The INSERT INTO statement (Chapter 5) adds new data, but to modify existing records, you use the UPDATE statement.

The UPDATE statement is a critical DML (Data Manipulation Language) command that allows you to change the values of one or more columns in one or more rows within a table. Understanding how to use it safely and effectively is paramount for any dynamic web application.

The Purpose of UPDATE

The UPDATE statement is used to modify existing records in a database table. You specify which table to update, which columns to change, what their new values should be, and most importantly, which specific rows to apply these changes to.

Basic Structure:

SQL
UPDATE table_name
SET column1 = new_value1, column2 = new_value2, ...
WHERE condition;
  • UPDATE table_name: Specifies the table you want to modify.
  • SET column1 = new_value1, ...: A comma-separated list of column-value pairs, where column is the name of the column you want to change, and new_value is the new value it should hold.
  • WHERE condition: (CRITICAL!) A clause that specifies which rows should be updated. Only rows that satisfy this condition will be modified.

The Importance of the WHERE Clause

The WHERE clause in an UPDATE statement is arguably the most important part.

  • Without a WHERE clause, the UPDATE statement will affect every single row in the table! This is a common and potentially disastrous mistake. Always double-check your WHERE clause to ensure you are updating only the intended rows.
  • It uses the same comparison, logical, and special operators as the SELECT ... WHERE clause you learned in Chapter 7.

SQL Syntax & Examples

Let’s use our scriptbuzz_db and the data we’ve populated. Remember to run USE scriptbuzz_db; first.

1. Updating a Single Row

The most common scenario is updating a specific record, usually identified by its primary key.

Example 1: Update ‘john_doe’s email address Let’s change john_doe‘s email to john.new.email@example.com. We’ll use user_id to ensure we target the correct user.

SQL
UPDATE users
SET email = 'john.new.email@example.com'
WHERE user_id = 1;

What it does: Locates the user with user_id = 1 and updates only that user’s email column to the new value. All other columns for that user remain unchanged. All other users are unaffected.

Verification (run this SELECT after the UPDATE):

SQL
SELECT user_id, username, email FROM users WHERE user_id = 1;

Expected Output:

user_idusernameemail
1john_doejohn.new.email@example.com

2. Updating Multiple Columns in a Single Row

You can update several columns in one UPDATE statement by separating the SET assignments with commas.

Example 2: Update ‘admin_user’s email and set last_login to current timestamp

SQL
UPDATE users
SET
    email = 'new.admin@example.com',
    last_login = NOW() -- MySQL function to get current date and time
WHERE user_id = 2;

What it does: For the user with user_id = 2, both their email and last_login columns are updated. NOW() is a MySQL function that returns the current date and time.

Verification:

SQL
SELECT user_id, username, email, last_login FROM users WHERE user_id = 2;

Expected Output (example – last_login will be current time):

user_idusernameemaillast_login
2admin_usernew.admin@example.com2025-06-29 21:35:00

3. Updating Multiple Rows

The WHERE clause allows you to target multiple rows that meet your condition.

Example 3: Put all products with stock_quantity 0 to inactive status

SQL
UPDATE products
SET is_active = 0
WHERE stock_quantity = 0;

What it does: Finds all products where stock_quantity is currently 0 and sets their is_active status to 0 (inactive). This would affect ‘Webcam 1080p’ and potentially others.

Verification:

SQL
SELECT name, stock_quantity, is_active FROM products WHERE stock_quantity = 0;

Expected Output (example):

namestock_quantityis_active
Webcam 1080p00

Example 4: Increase price of all ‘Electronics’ category products by 10%

You can use expressions in the SET clause.

SQL
UPDATE products
SET price = price * 1.10 -- Increase current price by 10%
WHERE category = 'Electronics';

What it does: For every product whose category is ‘Electronics’, its price is updated to 110% of its current value.

Verification:

SQL
SELECT name, category, price FROM products WHERE category = 'Electronics';

Expected Output (example, prices will be 10% higher than before):

namecategoryprice
Laptop ProElectronics1320.00
Gaming MonitorElectronics385.00
Gaming MouseElectronics82.50

4. Updating All Rows (No WHERE Clause) – USE WITH EXTREME CAUTION!

If you omit the WHERE clause, the UPDATE statement will apply the changes to every single row in the table.

Example 5: Set is_active to 1 for ALL products (NOT RECOMMENDED without a good reason!)

SQL
-- DANGER ZONE! Only run if you truly understand the implications.
UPDATE products
SET is_active = 1;

What it does: This query would make every single product active, regardless of its current is_active status or stock_quantity.

Rule of Thumb: Before running an UPDATE statement without a WHERE clause, ask yourself: “Do I really want to change every record in this table?” In most cases, the answer is “no.”

Common Pitfalls and Best Practices

  • Always Test WHERE Clauses with SELECT First: Before running a destructive UPDATE or DELETE query, first run a SELECT query with the exact same WHERE clause. This allows you to see which rows will be affected before you make changes.
SQL
-- See which products will be affected
SELECT name, price FROM products WHERE category = 'Electronics';
-- Then run the UPDATE if the selection is correct
-- UPDATE products SET price = price * 1.10 WHERE category = 'Electronics';

  • Backup Before Large Updates: For critical data or large-scale updates, always perform a database backup beforehand.
  • Transactions (Advanced): In real-world applications, especially for complex updates involving multiple tables, you’d use SQL transactions. Transactions allow you to group multiple SQL statements into a single, atomic unit of work. If any statement fails, or if you decide to ROLLBACK, all changes are undone. This ensures data consistency. We’ll briefly touch on this in a later chapter.
  • Security (SQL Injection): Just like with INSERT, never put raw user input directly into your UPDATE queries. Always use prepared statements with parameter binding to prevent SQL Injection vulnerabilities.

Notes:

  • The UPDATE statement is used to modify existing data in a table.
  • Syntax: UPDATE table_name SET column1 = new_value1, ... WHERE condition;
  • The WHERE clause is CRITICAL for specifying which rows to update. Without it, all rows in the table will be affected.
  • You can update single columns or multiple columns in one statement.
  • You can use expressions (like price = price * 1.10) in the SET clause.
  • Always test your WHERE clause with a SELECT statement before executing a destructive UPDATE.

Small Exercise

Using your scriptbuzz_db:

  1. Update the stock_quantity of ‘Wireless Mouse’ to 150.
  2. Change the is_admin status of user_id = 1 (‘john_doe’) to 1 (make them an admin).
  3. If you have an orders table, update the status of any order with a total_amount less than 50.00 to ‘cancelled’.
  4. If you have a posts table, update the content of a specific post (e.g., post_id = 1) to a new, shorter text, and also set its updated_at timestamp to NOW().

You’re now able to change data in your database! Next, we’ll learn how to remove data with the DELETE statement.

📘 Continue to the Next Chapter

Deleting Data with DELETE

Understand how to permanently remove records from your database using the DELETE statement with precise control.

➡️

🚀 Explore Popular Learning Tracks