Data does not stay still. An employee moves to another city, gets a raise, or leaves the company. An order changes from Pending to Shipped. A spam account has to be removed. So far we have created tables, inserted rows and read them back. Now we will learn the two statements that change and remove existing rows: UPDATE and DELETE.
Both are simple to write and very easy to get wrong. One missing WHERE clause can overwrite every salary in the company or empty an entire table in a fraction of a second. So along with the syntax, this chapter teaches the safety habits that professionals use every day.
Examples use the shared company_db sample database with real MySQL 8.4 output. Since these statements change data, each example starts from a freshly loaded company_db, except where a step clearly continues the previous one (for example, running the same UPDATE a second time). On your machine, you can reset the data at any time by running the company_db.sql script again (see Setting Up MySQL); it drops and recreates the whole database.
UPDATE: Changing Existing Rows
UPDATE modifies the values of one or more columns in the rows of a table that satisfy a condition.
UPDATE table_name
SET column1 = value1,
column2 = value2, ...
WHERE condition;UPDATE table_namesays which table to change.SETlists the columns and their new values.WHEREdecides which rows change. It works exactly like the WHERE clause you already know from SELECT. If you leave it out, every row changes.
Updating One Row
Ishita Gupta's city is not recorded yet. She tells HR she lives in Noida. Look at the row first:
SELECT emp_id, name, city FROM employees WHERE emp_id = 10;+--------+--------------+------+
| emp_id | name | city |
+--------+--------------+------+
| 10 | Ishita Gupta | NULL |
+--------+--------------+------+
1 row in set (0.00 sec)UPDATE employees SET city = 'Noida' WHERE emp_id = 10;Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0SELECT emp_id, name, city FROM employees WHERE emp_id = 10;+--------+--------------+-------+
| emp_id | name | city |
+--------+--------------+-------+
| 10 | Ishita Gupta | Noida |
+--------+--------------+-------+
1 row in set (0.00 sec)Why WHERE emp_id = 10 and not WHERE name = 'Ishita Gupta'? Because emp_id is the primary key. It is guaranteed to identify exactly one row. Two employees could one day share a name; they can never share a primary key.
Rows matched vs Changed
Look closely at MySQL's message: Rows matched: 1 Changed: 1. Run the same UPDATE a second time:
UPDATE employees SET city = 'Noida' WHERE emp_id = 10;Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0The WHERE clause still matched one row, but the city was already Noida, so nothing actually changed. MySQL does not rewrite a value that is already the same, and "rows affected" reports changed rows. This is useful when debugging: Rows matched: 0 means your WHERE clause found nothing (maybe a typo), while matched 1, changed 0 means the data was already correct.
Updating Several Columns at Once
Separate the assignments with commas. Ishita is assigned to HR (department 30) and gets an official email:
UPDATE employees
SET dept_id = 30,
email = 'ishita@company.com'
WHERE emp_id = 10;Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0Even though two columns changed, it is still 1 row affected. MySQL counts rows, not columns.
SELECT emp_id, name, email, dept_id, city FROM employees WHERE emp_id = 10;+--------+--------------+--------------------+---------+-------+
| emp_id | name | email | dept_id | city |
+--------+--------------+--------------------+---------+-------+
| 10 | Ishita Gupta | ishita@company.com | 30 | Noida |
+--------+--------------+--------------------+---------+-------+
1 row in set (0.01 sec)A common beginner mistake is writing SET dept_id = 30 AND email = '...'. That is not two assignments; it is one assignment of the boolean expression 30 AND (email = '...') to dept_id. Always use commas between assignments.
Updating Many Rows with an Expression
The new value can be calculated from the old one. The Sales department (20) gets a 10% raise. Check who is affected first:
SELECT emp_id, name, salary FROM employees WHERE dept_id = 20;+--------+--------------+-----------+
| emp_id | name | salary |
+--------+--------------+-----------+
| 4 | Sneha Kapoor | 110000.00 |
| 5 | Vikram Singh | 62000.00 |
| 6 | Ananya Das | 58000.00 |
+--------+--------------+-----------+
3 rows in set (0.00 sec)UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 20;Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0SELECT emp_id, name, salary FROM employees WHERE dept_id = 20;+--------+--------------+-----------+
| emp_id | name | salary |
+--------+--------------+-----------+
| 4 | Sneha Kapoor | 121000.00 |
| 5 | Vikram Singh | 68200.00 |
| 6 | Ananya Das | 63800.00 |
+--------+--------------+-----------+
3 rows in set (0.00 sec)How does salary = salary * 1.10 work? For each matching row, MySQL reads that row's current salary on the right side, computes the new value, and stores it. Each row uses its own old value: 110000 becomes 121000, 62000 becomes 68200, and so on.
If the WHERE matches nothing, nothing happens and there is no error:
UPDATE employees SET salary = salary + 5000 WHERE dept_id = 99;Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0Using Complex Conditions
Any WHERE condition works: AND, OR, IN, BETWEEN, LIKE, IS NULL. Mark every pending order placed before April 2024 as delivered:
UPDATE orders
SET status = 'Delivered'
WHERE status = 'Pending' AND order_date < '2024-04-01';Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0Only order 1007 (28 March) matched; order 1008 (2 April) is still pending. If you are writing SQL inside a program, the function ROW_COUNT() returns how many rows the last UPDATE or DELETE changed:
SELECT ROW_COUNT();+-------------+
| ROW_COUNT() |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)Setting a Value to NULL
To clear a value, assign NULL with =. (In SET, = means assignment, not comparison, so this is fine. In WHERE you would still need IS NULL.)
UPDATE employees SET email = NULL WHERE emp_id = 9;Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0This works only if the column allows NULL. Setting salary (declared NOT NULL) to NULL is rejected. Constraints are covered in detail in SQL Constraints.
How SET Evaluates: A MySQL Gotcha
When one UPDATE sets several columns, does the second assignment see the old or the new value of the first? Let's test on a tiny table:
CREATE TABLE swap_demo (id INT PRIMARY KEY, a INT, b INT);
INSERT INTO swap_demo VALUES (1, 10, 20);
UPDATE swap_demo SET a = b, b = a WHERE id = 1;
SELECT * FROM swap_demo;+----+------+------+
| id | a | b |
+----+------+------+
| 1 | 20 | 20 |
+----+------+------+
1 row in set (0.00 sec)The "swap" failed! MySQL evaluates single-table UPDATE assignments left to right, and each assignment sees the values already changed by earlier ones:
Start: a = 10, b = 20
SET a = b -> a = 20 (b is still 20)
SET b = a -> b = 20 (a is ALREADY 20, not 10)
Result: a = 20, b = 20Another example:
UPDATE swap_demo SET a = 5, b = a * 2 WHERE id = 1;
SELECT * FROM swap_demo;+----+------+------+
| id | a | b |
+----+------+------+
| 1 | 5 | 10 |
+----+------+------+
1 row in set (0.00 sec)b became 5 * 2 = 10, using the new a. The SQL standard says every right-hand side should use the old row values (so the swap would work), and PostgreSQL, SQL Server and Oracle follow the standard. MySQL is different here. This is a favourite interview trick question, and a real source of bugs when moving SQL between databases. In MySQL, to swap two columns use a temporary holder, for example a third column or a self-join.
UPDATE with ORDER BY and LIMIT (MySQL)
MySQL lets a single-table UPDATE (and DELETE) use ORDER BY and LIMIT to change only the first N matching rows. Ship the oldest pending order:
SELECT order_id, order_date, status FROM orders
WHERE status = 'Pending' ORDER BY order_date;+----------+------------+---------+
| order_id | order_date | status |
+----------+------------+---------+
| 1007 | 2024-03-28 | Pending |
| 1008 | 2024-04-02 | Pending |
+----------+------------+---------+
2 rows in set (0.00 sec)UPDATE orders
SET status = 'Shipped'
WHERE status = 'Pending'
ORDER BY order_date
LIMIT 1;Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0SELECT order_id, order_date, status FROM orders WHERE order_id IN (1007, 1008);+----------+------------+---------+
| order_id | order_date | status |
+----------+------------+---------+
| 1007 | 2024-03-28 | Shipped |
| 1008 | 2024-04-02 | Pending |
+----------+------------+---------+
2 rows in set (0.00 sec)Without ORDER BY, LIMIT 1 would update "some" matching row, and which one is not guaranteed. A real use of this pattern is processing a big job in small batches (e.g. LIMIT 1000 repeated in a loop) so the table is never locked for long. This is MySQL-specific syntax; standard SQL does not allow it.
UPDATE Using Values from Another Table
The WHERE clause can contain a subquery (a SELECT inside another statement; Subqueries has a full chapter). Put every order from customers in Mumbai on hold:
UPDATE orders
SET status = 'On Hold'
WHERE customer_id IN (SELECT customer_id FROM customers WHERE city = 'Mumbai');Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0SELECT order_id, customer_id, status FROM orders WHERE customer_id = 104;+----------+-------------+---------+
| order_id | customer_id | status |
+----------+-------------+---------+
| 1005 | 104 | On Hold |
+----------+-------------+---------+
1 row in set (0.00 sec)Error 1093: Reading the Table You Are Updating
Now try to give a 2000 raise to everyone paid below the average salary:
UPDATE employees
SET salary = salary + 2000
WHERE salary < (SELECT AVG(salary) FROM employees);ERROR 1093 (HY000): You can't specify target table 'employees' for update in FROM clauseMySQL does not allow a subquery to read directly from the same table that the UPDATE (or DELETE) is modifying. The reason: as rows get updated, the average would keep changing in the middle of the statement, and the result could depend on the order rows were processed. The standard workaround is to wrap the subquery in a derived table, which forces MySQL to calculate the value once, up front:
UPDATE employees
SET salary = salary + 2000
WHERE salary < (SELECT avg_sal
FROM (SELECT AVG(salary) AS avg_sal FROM employees) AS t);Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0The average was 82500, so the six employees earning less than that (Vikram, Ananya, Karan, Meera, Arjun and Ishita) each got 2000 more. MySQL also supports a multi-table UPDATE ... JOIN syntax, which you will be able to read once you have learned SQL Joins.
UPDATE and Foreign Keys
Recall that employees.dept_id references departments.dept_id. MySQL will not let an UPDATE break that link:
UPDATE employees SET dept_id = 99 WHERE emp_id = 8;ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`))There is no department 99, so the change is refused. The same protection applies in the other direction. Sneha (emp_id 4) is the manager of Vikram and Ananya, so her id cannot simply change:
UPDATE employees SET emp_id = 11 WHERE emp_id = 4;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company_db`.`employees`, CONSTRAINT `employees_ibfk_2` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`emp_id`))Changing a key that nobody references is allowed (for example, Research, department 50, has no employees, so UPDATE departments SET dept_id = 60 WHERE dept_id = 50 succeeds). In general, though, avoid updating primary keys: other tables, URLs, logs and caches may all be pointing at that value. SQL Constraints shows how ON UPDATE CASCADE and ON DELETE CASCADE change this behaviour.
DELETE: Removing Rows
DELETE removes whole rows from a table that satisfy a condition. The table and its structure stay; only the rows are gone.
DELETE FROM table_name
WHERE condition;Notice there is no column list. You cannot delete "one column of a row"; to clear a single value, use UPDATE ... SET col = NULL. DELETE always removes complete rows.
Deleting One Row
Order 1003 was cancelled and the business wants it removed:
SELECT * FROM orders WHERE order_id = 1003;+----------+-------------+------------+---------+-----------+
| order_id | customer_id | order_date | amount | status |
+----------+-------------+------------+---------+-----------+
| 1003 | 101 | 2024-02-02 | 1200.00 | Cancelled |
+----------+-------------+------------+---------+-----------+
1 row in set (0.00 sec)DELETE FROM orders WHERE order_id = 1003;Query OK, 1 row affected (0.01 sec)SELECT order_id, customer_id, status FROM orders ORDER BY order_id;+----------+-------------+-----------+
| order_id | customer_id | status |
+----------+-------------+-----------+
| 1001 | 101 | Delivered |
| 1002 | 102 | Delivered |
| 1004 | 103 | Delivered |
| 1005 | 104 | Shipped |
| 1006 | 102 | Delivered |
| 1007 | 101 | Pending |
| 1008 | NULL | Pending |
+----------+-------------+-----------+
7 rows in set (0.00 sec)Deleting with a Condition
Remove pending guest orders (orders with no customer):
DELETE FROM orders WHERE status = 'Pending' AND customer_id IS NULL;Query OK, 1 row affected (0.01 sec)Remember the NULL rules. If you write customer_id = NULL instead, the condition is UNKNOWN for every row and nothing is deleted. There is no error, just a silent "0 rows":
DELETE FROM orders WHERE customer_id = NULL;Query OK, 0 rows affected (0.00 sec)DELETE with ORDER BY and LIMIT
Just like UPDATE, MySQL's DELETE accepts ORDER BY and LIMIT. Delete the two oldest orders:
DELETE FROM orders ORDER BY order_date LIMIT 2;Query OK, 2 rows affected (0.01 sec)On fresh data this removes orders 1001 (15 Jan) and 1002 (20 Jan). Archiving old log rows in batches, such as DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 5000 run repeatedly, is a typical production use.
DELETE and Foreign Keys
You cannot delete a parent row that child rows still point to:
DELETE FROM departments WHERE dept_id = 30;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `departments` (`dept_id`))HR still has Karan and Meera. Department 50 (Research) has no employees, so it can be deleted:
DELETE FROM departments WHERE dept_id = 50;Query OK, 1 row affected (0.01 sec)The self-referencing key on manager_id works the same way. Sneha (4) manages two people, so DELETE FROM employees WHERE emp_id = 4 fails with error 1451, while Ananya (6), who manages nobody, can be deleted. Likewise customer 105 has no orders and can be deleted, but customer 101 cannot while orders 1001, 1003 and 1007 point to it.
This protection is a feature, not an annoyance: it stops you from leaving "orphan" rows, such as employees in a department that no longer exists. The right order is: deal with the children first (move or delete them), then delete the parent.
DELETE Without WHERE
DELETE FROM orders;Query OK, 8 rows affected (0.01 sec)Every row is gone. The table still exists (with its columns and constraints) but it is empty. Occasionally that is exactly what you want; much more often it is an accident. If you really want to empty a big table, TRUNCATE TABLE is faster; the next chapter, ALTER, DROP & TRUNCATE, compares DELETE, TRUNCATE and DROP in detail.
Safety Habits for UPDATE and DELETE
Every experienced developer has a story about a missing WHERE clause. Here is how to make sure it does not become your story.
1. SELECT First, Then Change
Write the statement as a SELECT with the exact same WHERE. Check that the rows are the ones you expect, and how many there are. Then replace the beginning with UPDATE or DELETE:
-- Step 1: check
SELECT * FROM orders WHERE status = 'Pending' AND customer_id IS NULL;
-- Step 2: same WHERE, now delete
DELETE FROM orders WHERE status = 'Pending' AND customer_id IS NULL;Then compare: the SELECT showed 1 row, and DELETE reported 1 row affected. If those numbers ever disagree, stop and investigate.
2. Use a Transaction So You Can Undo
By default MySQL is in autocommit mode: each statement is saved permanently the moment it finishes. If you start a transaction first, nothing is permanent until you say COMMIT, and ROLLBACK undoes everything:
START TRANSACTION;
DELETE FROM orders; -- oops, forgot the WHERE
SELECT COUNT(*) FROM orders;+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)ROLLBACK;
SELECT COUNT(*) FROM orders;+----------+
| COUNT(*) |
+----------+
| 8 |
+----------+
1 row in set (0.00 sec)All 8 orders are back. (This works with InnoDB tables, MySQL's default storage engine.) If the result had been correct, you would type COMMIT; instead. Transactions in SQL explains this mechanism fully.
3. Turn On Safe Updates Mode
MySQL has a safety option, sql_safe_updates. When it is on, UPDATE and DELETE are refused unless the WHERE clause uses a key column (or there is a LIMIT):
SET SESSION sql_safe_updates = 1;
UPDATE employees SET city = 'Delhi';ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. Even a WHERE on a non-indexed column is blocked, because MySQL cannot be sure it is narrow:
UPDATE employees SET city = 'Delhi' WHERE city = 'Kochi';ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. But a WHERE on the primary key is allowed:
UPDATE employees SET city = 'Delhi' WHERE emp_id = 8;Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0You can start the command-line client with this mode always on: mysql --safe-updates (nicknamed --i-am-a-dummy, a real option name!). MySQL Workbench turns it on by default, which is why Workbench users sometimes meet error 1175.
4. Prefer Soft Deletes for Important Data
Many real applications never physically delete important rows such as users or orders. Instead they keep a column such as is_deleted or deleted_at and run an UPDATE:
UPDATE users SET deleted_at = NOW() WHERE user_id = 42; -- "delete"
SELECT * FROM users WHERE deleted_at IS NULL; -- show only active usersThis keeps history, makes "undo" trivial, and keeps foreign keys valid. The cost: every query must remember to filter out deleted rows, and personal data you are legally required to erase still needs a real DELETE.
UPDATE vs DELETE at a Glance
Point | UPDATE | DELETE |
|---|---|---|
What it changes | Values in existing rows | Removes whole rows |
Row count afterwards | Same | Fewer |
Column list | Yes, in SET | None (always whole rows) |
Without WHERE | Every row changed | Every row removed |
ORDER BY / LIMIT | Allowed in MySQL (single table) | Allowed in MySQL (single table) |
Foreign keys | New value must exist in parent; parent key in use cannot change | Parent row in use cannot be deleted |
Can be rolled back | Yes, inside a transaction | Yes, inside a transaction |
Category | DML (Data Manipulation Language) | DML |
Common Mistakes
Mistake | What happens | Fix |
|---|---|---|
Forgetting WHERE | Every row updated or deleted | SELECT first; use transactions; enable safe updates |
| Only | Use commas: |
| 0 rows affected, silently |
|
Identifying rows by name instead of key | May change several people with the same name | Use the primary key in WHERE |
Swapping columns with | Both end up equal | Remember MySQL evaluates SET left to right |
Subquery reading the table being updated | Error 1093 | Wrap it in a derived table |
Deleting a parent before its children | Error 1451 | Move or delete child rows first (or use a cascade rule) |
LIMIT without ORDER BY | Unpredictable choice of rows | Always pair LIMIT with ORDER BY |
Interview Questions
What happens if you run UPDATE or DELETE without a WHERE clause?
The statement applies to every row in the table: UPDATE changes all rows, DELETE removes all rows (the table itself remains).
Can you delete a single column value with DELETE?
No. DELETE removes entire rows. To clear one value use UPDATE ... SET col = NULL; to remove a column from the table use ALTER TABLE ... DROP COLUMN.
What is the difference between "rows matched" and "rows changed" in MySQL?
Matched is how many rows satisfied the WHERE clause; changed is how many actually got a different value. Rows that already had the new value are matched but not changed.
In MySQL, what does UPDATE t SET a = b, b = a do?
MySQL evaluates single-table assignments left to right using already-updated values, so both columns end up with the original value of b. Standard SQL (and PostgreSQL, SQL Server, Oracle) would swap them.
How do you give a 10% raise to everyone in a department?
UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 20; Each row's new value is computed from its own old value.
Why does MySQL give error 1093?
A subquery in UPDATE/DELETE reads from the same table being modified. Wrap the subquery in a derived table (SELECT ... FROM (subquery) AS t) so it is materialised first, or use a join.
Can a DELETE be undone?
Yes, if it was run inside a transaction that has not been committed yet (ROLLBACK). In autocommit mode it is permanent immediately, and you would need a backup.
What is a soft delete?
Marking a row as deleted with a flag or timestamp column (via UPDATE) instead of removing it, so data can be restored and history is kept. Queries must then filter out the deleted rows.
Why can't I delete a row from a parent table?
A foreign key in a child table still references it (error 1451). Delete or reassign the child rows first, or define the foreign key with ON DELETE CASCADE / SET NULL.
Summary
UPDATE table SET col = value, ... WHERE conditionchanges values in matching rows; the new value can use the old one (salary = salary * 1.10).DELETE FROM table WHERE conditionremoves whole matching rows.Without WHERE, all rows are affected. Use the primary key to target exactly one row.
MySQL reports rows matched vs changed;
ROW_COUNT()gives the count in code.MySQL evaluates SET assignments left to right with updated values, unlike standard SQL.
MySQL allows ORDER BY and LIMIT on single-table UPDATE/DELETE; subqueries on the same table need a derived-table wrapper (error 1093).
Foreign keys block changes that would leave orphan rows (errors 1451, 1452).
Safety habits: SELECT first, use transactions (ROLLBACK), enable
sql_safe_updates, consider soft deletes.
UPDATE and DELETE change the data. In the next chapter, ALTER, DROP & TRUNCATE, we will change the structure of tables themselves, and settle the classic interview question: DELETE vs TRUNCATE vs DROP.