UPDATE

Data changes all the time. A product's price goes up, a customer moves from Jaipur to Pune, an order changes from shipped to delivered. The UPDATE statement changes values in rows that already exist.

UPDATE is simple to write and easy to get badly wrong: one missing line can change every row in a table. In this lesson you will learn how to update exactly the rows you mean, how to check before you change anything, and how MySQL's safe-updates mode protects you.

The basic UPDATE

An UPDATE names the table, says which columns get which new values after SET, and uses WHERE to choose the rows:

UPDATE products
SET price = 749.00
WHERE product_id = 1;

SELECT product_id, product_name, price
FROM products
WHERE product_id = 1;

product_id | product_name   | price
-----------+----------------+------
1          | Wireless Mouse | 749
(1 row)

Only the Wireless Mouse changed, because only one row has product_id 1. Filtering on the primary key (the column that uniquely identifies each row) is the safest way to update a single row: it can never match more than one.

The most important line is WHERE

The WHERE clause decides which rows change. Leave it out and every row in the table changes:

-- Meant to change one product, but there is no WHERE:
UPDATE products SET price = 749.00;

That single statement sets all twelve products to ₹749, including the ₹15,999 monitor. There is no undo button. Unless you were inside a transaction (see below), the only way back is a backup.

A habit that prevents this: write the SELECT first. Before you update, run a SELECT with exactly the same WHERE clause and look at the rows it returns. When the list is right, change SELECT ... FROM into UPDATE ... SET and keep the WHERE untouched.

Changing several columns and calculating values

Separate several assignments with commas. A new value can be calculated from the old one. Here the shop raises Audio prices by 10 percent and receives 10 more units of each Audio product:

SELECT product_name, price, stock FROM products WHERE category = 'Audio';

UPDATE products
SET price = ROUND(price * 1.10, 2),
    stock = stock + 10
WHERE category = 'Audio';

SELECT product_name, price, stock FROM products WHERE category = 'Audio';

product_name                | price | stock
----------------------------+-------+------
Noise-Cancelling Headphones | 8999  | 25
Bluetooth Speaker           | 2499  | 40
(2 rows)

product_name                | price  | stock
----------------------------+--------+------
Noise-Cancelling Headphones | 9898.9 | 35
Bluetooth Speaker           | 2748.9 | 50
(2 rows)

On the right of =, price and stock mean the values before the update. So stock = stock + 10 adds 10 to whatever was there. ROUND(..., 2) keeps the price to paise. MySQL would print 9898.90 because the column has two decimal places; the browser prints 9898.9.

The WHERE here is not a key: it matches every product in a category. That is fine when it is what you want, which is why the first SELECT is there to show you the rows before they change.

Updating with conditions on dates and status

WHERE can combine conditions just like in a SELECT (see AND, OR & NOT). Suppose the courier confirms that everything shipped before April has been delivered:

UPDATE orders
SET status = 'delivered'
WHERE status = 'shipped'
  AND order_date < '2025-04-01';

SELECT order_id, order_date, status
FROM orders
WHERE order_id IN (108, 113);

order_id | order_date | status
---------+------------+----------
108      | 2025-03-15 | delivered
113      | 2025-05-02 | shipped
(2 rows)

Order 108 was shipped in March, so it changed. Order 113 was shipped in May, so it did not.

Using values from another table

Sometimes the new value lives in another table. When Aarav's pending order 115 is packed, the stock of each product in it should go down by the quantity ordered. A correlated subquery (see Subqueries) looks up the quantity for each product:

UPDATE products
SET stock = stock - (
  SELECT SUM(oi.quantity)
  FROM order_items oi
  WHERE oi.order_id = 115
    AND oi.product_id = products.product_id
)
WHERE product_id IN (SELECT product_id FROM order_items WHERE order_id = 115);

SELECT product_id, product_name, stock
FROM products
WHERE product_id IN (5, 7);

product_id | product_name      | stock
-----------+-------------------+------
5          | Laptop Stand      | 74
7          | Bluetooth Speaker | 39
(2 rows)

The outer WHERE is essential. Without it, products that are not in order 115 would get stock - NULL, which is NULL.

MySQL also has a shorter form that joins the tables directly. It is not standard SQL, so it does not run in the browser:

UPDATE products p
JOIN order_items oi ON oi.product_id = p.product_id
SET p.stock = p.stock - oi.quantity
WHERE oi.order_id = 115;

MySQL's safe-updates mode

MySQL can refuse risky updates for you. When safe-updates mode is on, an UPDATE or DELETE is only allowed if its WHERE uses a key column (such as the primary key) or the statement has a LIMIT. A statement without a proper WHERE is stopped with error 1175 ("You are using safe update mode...") instead of changing every row.

SET sql_safe_updates = 1;

UPDATE products SET price = 749.00;                          -- refused: error 1175
UPDATE products SET price = 749.00 WHERE product_id = 1;     -- allowed

MySQL Workbench turns this mode on by default, and the mysql command-line client turns it on when you start it with --safe-updates. It also blocks some correct statements, such as the Audio price rise above, because category is not a key. Turn it off for one session with SET sql_safe_updates = 0; only when you are sure, and turn it back on afterwards.

How many rows changed?

After an UPDATE, the MySQL command-line client reports something like Rows matched: 2 Changed: 2 Warnings: 0. Matched is how many rows the WHERE found; changed is how many actually got a different value. If you set a price to the value it already has, the row is matched but not changed. In PHP, PDO's rowCount() returns the number of changed rows, which is a simple way for your code to check that an update did what you expected.

Common mistakes

• Forgetting WHERE. Every row changes. Write the SELECT first, and keep safe-updates mode on while you learn.
• Using = to find NULL. WHERE email = NULL matches nothing. Use WHERE email IS NULL (see NULL Values). To set a column to empty, SET email = NULL is correct.
• Repeating SET. Write SET a = 1, b = 2, not SET a = 1 SET b = 2.
• Updating with text built from user input. Use prepared statements in your application code, as in CRUD with Prepared Statements.

Key points

• UPDATE table SET column = value WHERE condition changes existing rows.
• Without WHERE, every row in the table is changed.
• Run a SELECT with the same WHERE first to see exactly which rows will change.
• New values can be calculated from old ones, such as stock = stock + 10.
• A subquery, or MySQL's UPDATE ... JOIN, can take new values from another table.
• Safe-updates mode (error 1175) blocks updates and deletes that have no key-based WHERE or LIMIT.

SELECT customer_id, first_name, city, state, email
FROM customers
WHERE customer_id = 6;

UPDATE customers
SET city = 'Pune',
    state = 'Maharashtra',
    email = 'meera.n@example.com'
WHERE customer_id = 6;

SELECT customer_id, first_name, city, state, email
FROM customers
WHERE customer_id = 6;