UPDATE with WHERE clause in MySQL

Introduction

The UPDATE statement changes existing data in a table. The WHERE clause limits which rows get changed.

You want to fix a wrong phone number for a specific customer.

You need to mark only overdue invoices as 'late'.

You want to increase the price of a product only if it is in a certain category.

You want to update the status of orders placed before a certain date.

Syntax

MySQL

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

The WHERE clause is important to avoid changing all rows by mistake.

You can update one or many columns at the same time.

Examples

Updates salary only for the employee with ID 101.

MySQL

UPDATE employees
SET salary = 5000
WHERE employee_id = 101;

Increases price by 10% for all products in the 'Books' category.

MySQL

UPDATE products
SET price = price * 1.1
WHERE category = 'Books';

Sets status and shipped date for a specific order.

MySQL

UPDATE orders
SET status = 'shipped', shipped_date = '2024-06-01'
WHERE order_id = 2001;

Sample Program

This example creates a customers table, inserts three rows, then updates Bob's city to San Francisco. Finally, it shows all customers.

MySQL

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  city VARCHAR(50)
);

INSERT INTO customers (id, name, city) VALUES
(1, 'Alice', 'New York'),
(2, 'Bob', 'Los Angeles'),
(3, 'Charlie', 'Chicago');

UPDATE customers
SET city = 'San Francisco'
WHERE name = 'Bob';

SELECT * FROM customers ORDER BY id;

Important Notes

Always double-check your WHERE clause to avoid updating unintended rows.

If you omit the WHERE clause, all rows in the table will be updated.

You can use multiple conditions in WHERE with AND/OR to be more specific.

Summary

UPDATE changes data in existing rows.

WHERE clause controls which rows get updated.

Be careful: no WHERE means update all rows.