UPDATE with a WHERE clause
35 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)Data changes after it is saved: a price drops, a train is delayed, a teacher corrects a mark. UPDATE changes values in rows that already exist, and its WHERE clause decides which rows.
UPDATE ... SET ... WHERE
UPDATE changes values in rows that already exist. UPDATE table SET column = value WHERE condition changes the column in every row where the condition is true.
Product 8 has stock 0. Example 1 changes only that row and shows it before and after.
Example 1 · Restock one product
SELECT product_id, name, stock FROM products WHERE product_id = 8;
UPDATE products SET stock = 25 WHERE product_id = 8;
SELECT product_id, name, stock FROM products WHERE product_id = 8;+------------+---------------------+-------+ | product_id | name | stock | +------------+---------------------+-------+ | 8 | Logitech M331 Mouse | 0 | +------------+---------------------+-------+ +------------+---------------------+-------+ | product_id | name | stock | +------------+---------------------+-------+ | 8 | Logitech M331 Mouse | 25 | +------------+---------------------+-------+
What this code does
- The Logitech mouse was out of stock.
WHERE product_id = 8matches exactly one row, so only itsstockchanges from 0 to 25. - The
UPDATEitself prints nothing in this mode. TheSELECTbefore and after shows the change.
Without UPDATE you would have to delete a row and insert it again just to fix one value, and lose its id and links on the way. UPDATE keeps the row and changes only what you name. WHERE is a filter: WHERE product_id = 8 keeps only the row whose id is 8. A condition on the primary key matches at most one row, which makes it the safest kind of UPDATE. Conditions can use =, <, >, <=, >= and <> (not equal), and the Querying module covers WHERE in full.
WHERE, the UPDATE is not an error: it simply changes 0 rows.Updating several columns and using expressions
An expression in SET is a calculation that uses the values the row has now, such as stock = stock - 2 or price = price * 0.90. MySQL works it out separately for each matching row.
Example 2 changes two wallet columns in one statement. Example 3 puts a sale on a whole category, and Example 4 shows the left-to-right trap.
Example 2 · UPI wallet: two columns in one UPDATE
CREATE TABLE wallets ( upi_id VARCHAR(30) PRIMARY KEY, owner VARCHAR(40) NOT NULL, balance DECIMAL(10,2) NOT NULL CHECK (balance >= 0), last_txn DATE NULL);
INSERT INTO wallets (upi_id, owner, balance, last_txn) VALUES ('asha@okhdfc', 'Asha Rao', 1200.00, '2025-06-01'), ('ravi@ybl', 'Ravi Kumar', 300.00, '2025-05-28');
UPDATE walletsSET balance = balance - 250.00, last_txn = '2025-06-05'WHERE upi_id = 'asha@okhdfc';
SELECT * FROM wallets ORDER BY upi_id;+-------------+------------+---------+------------+ | upi_id | owner | balance | last_txn | +-------------+------------+---------+------------+ | asha@okhdfc | Asha Rao | 950.00 | 2025-06-05 | | ravi@ybl | Ravi Kumar | 300.00 | 2025-05-28 | +-------------+------------+---------+------------+
What this code does
- One
SETchanges two columns of Asha's row:balanceuses its own old value (1200.00 - 250.00 = 950.00) andlast_txngets a fixed date. - Ravi's row does not match the
WHERE, so it keeps 300.00 and its old date.
Example 3 · Festival sale: 10% off every book
UPDATE productsSET price = price * 0.90WHERE category_id = 5;
SELECT product_id, name, priceFROM productsWHERE category_id = 5ORDER BY product_id;+------------+-----------------+--------+ | product_id | name | price | +------------+-----------------+--------+ | 9 | Clean Code | 449.10 | | 10 | Hands-On Python | 585.00 | | 11 | Atomic Habits | 359.10 | +------------+-----------------+--------+
What this code does
- One statement changes three rows: every product in category 5. Each row's new price is worked out from that row's own old price.
priceisDECIMAL(10,2), so 499.00 × 0.90 is stored exactly as 449.10. Money columns should beDECIMAL, neverFLOAT.
Example 4 · Trap: MySQL applies SET left to right
CREATE TABLE revaluation ( roll_no INT PRIMARY KEY, marks INT NOT NULL, old_marks INT NULL);INSERT INTO revaluation (roll_no, marks) VALUES (101, 60);
-- Wrong order: old_marks gets the new marksUPDATE revaluation SET marks = marks + 5, old_marks = marks WHERE roll_no = 101;SELECT * FROM revaluation;
-- Right order: save the old value firstUPDATE revaluation SET marks = 60, old_marks = NULL WHERE roll_no = 101;UPDATE revaluation SET old_marks = marks, marks = marks + 5 WHERE roll_no = 101;SELECT * FROM revaluation;+---------+-------+-----------+ | roll_no | marks | old_marks | +---------+-------+-----------+ | 101 | 65 | 65 | +---------+-------+-----------+ +---------+-------+-----------+ | roll_no | marks | old_marks | +---------+-------+-----------+ | 101 | 65 | 60 | +---------+-------+-----------+
What this code does
- In the first
UPDATE,marksbecomes 65 first, and thenold_marks = markscopies that new 65. The old 60 is lost. - Putting
old_marks = marksfirst saves 60 beforemarkschanges. In PostgreSQL every assignment sees the old values, so the order would not matter there; in MySQL it does.
Several assignments go after one SET, separated by commas: SET balance = balance - 250, last_txn = '2025-06-05'. All of them apply to the same matching rows.
An expression saves you from reading the old value first. Two things can go wrong. If the new value breaks a rule, such as a CHECK on marks, the whole statement fails and no row changes: ERROR 3819 (HY000): Check constraint 'enrollments_chk_1' is violated. And MySQL applies the assignments left to right, so a later assignment sees the new value of an earlier column. Standard SQL and most other databases use the old values; Example 4 shows the difference.
UPDATE without WHERE, and safe update mode
Safe update mode (SQL_SAFE_UPDATES = 1) makes MySQL refuse an UPDATE or DELETE unless its WHERE uses a key column or the statement has a LIMIT. It protects you when you forget the WHERE.
Example 5 runs an UPDATE with no WHERE, and every customer ends up in Pune. Safe update mode would have stopped it.
SET SQL_SAFE_UPDATES = 1;
UPDATE products SET price = price * 1.10;ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
Example 5 · Mistake: no WHERE, every row changes
UPDATE customers SET city = 'Pune';
SELECT customer_id, name, city FROM customers ORDER BY customer_id;+-------------+--------------+------+ | customer_id | name | city | +-------------+--------------+------+ | 1 | Asha Rao | Pune | | 2 | Ravi Kumar | Pune | | 3 | Meena Iyer | Pune | | 4 | Kabir Singh | Pune | | 5 | Priya Sharma | Pune | | 6 | Arjun Mehta | Pune | | 7 | Divya Nair | Pune | | 8 | Rahul Verma | Pune | | 9 | Sneha Patil | Pune | | 10 | Imran Khan | Pune | +-------------+--------------+------+
What this code does
- The intent was to move one customer, but there is no
WHERE, so all 10 rows now say Pune. MySQL gave no warning. - The fix is
WHERE customer_id = 3. With safe update mode on, this statement would have been refused with error 1175 instead.
An UPDATE without WHERE changes every row in the table. MySQL does not ask "are you sure?": the statement runs, and all ten customers move to Pune in a fraction of a second.
A missing WHERE, or one that matches more than you thought, is one of the most common and costly mistakes with SQL: it can wipe out the prices of a whole store. Safe update mode is the guard against it, and a key column is an indexed column such as the primary key. MySQL Workbench turns it on by default for its query tabs; the command-line client has it off unless you start it with mysql --safe-updates.
SET SQL_SAFE_UPDATES = 1;turns it on for your session;= 0turns it off.- In Workbench: Edit → Preferences (MySQL Workbench → Settings on a Mac) → SQL Editor → "Safe Updates". Reconnect after changing it.
- Safe mode also refuses
WHERE city = 'Delhi', becausecityhas no index. Use the primary key, or turn safe mode off only for that one statement.
Predict the output
How many rows change?
Use the ShopLite data. The SELECT counts the rows the UPDATE would match.
Round 1 of 2
UPDATE customers SET city = 'Noida' WHERE city = 'Delhi';
Check first with SELECT, then update
The check-first habit is running a SELECT with the exact WHERE of a planned UPDATE or DELETE, looking at the rows it returns, and only then changing SELECT into the change.
Example 6 looks at the matching orders first, then turns the same WHERE into an UPDATE.
Example 6 · Check first: cancel old unshipped orders
-- 1. See exactly which rows matchSELECT order_id, customer_id, order_date, statusFROM ordersWHERE status = 'PLACED' AND order_date < '2025-05-01';
-- 2. Same WHERE, now as an UPDATEUPDATE ordersSET status = 'CANCELLED'WHERE status = 'PLACED' AND order_date < '2025-05-01';
-- 3. Check the resultSELECT order_id, order_date, statusFROM ordersWHERE status IN ('PLACED', 'CANCELLED')ORDER BY order_id;+----------+-------------+------------+--------+ | order_id | customer_id | order_date | status | +----------+-------------+------------+--------+ | 11 | 8 | 2025-04-25 | PLACED | +----------+-------------+------------+--------+ +----------+------------+-----------+ | order_id | order_date | status | +----------+------------+-----------+ | 4 | 2025-02-10 | CANCELLED | | 11 | 2025-04-25 | CANCELLED | | 13 | 2025-05-09 | PLACED | | 14 | 2025-05-20 | CANCELLED | | 15 | 2025-06-01 | PLACED | +----------+------------+-----------+
What this code does
- Step 1 shows one row, order 11, so the
UPDATEwill change exactly one order.ANDmeans both conditions must be true. - Step 3 confirms it: order 11 is now
CANCELLED, while orders 13 and 15 (placed in May and June) are stillPLACED.IN (...)matches any value in the list.
If the SELECT returned the right rows, change only the start of the statement: SELECT ... FROM orders WHERE ... becomes UPDATE orders SET ... WHERE ..., with the WHERE copied as it is. Then run the SELECT again to see the result. An UPDATE cannot be undone with a key press. The Transactions module shows how to wrap changes in a transaction and undo them with ROLLBACK; until then, checking first is your safety net.
WHERE part; do not retype it. Most wrong updates come from a WHERE that was retyped slightly differently.Next step
Check your understanding
11 quick questions