Updating Data

UPDATE, and the Clause You Must Never Forget

UPDATE changes values in rows that already exist. It has three parts: the table, a SET list saying which columns take which new values, and a WHERE clause saying which rows are affected.

That third part is optional to the parser and mandatory in practice. An UPDATE with no WHERE rewrites every row in the table. Not "errors", not "asks are you sure" — it succeeds, quickly and quietly, and reports how many thousands of rows it changed. There is no undo unless you were inside a transaction or have a backup from before. Every experienced developer has done this once, and the memory is what makes them careful afterwards.

So build the habit now, before you have anything valuable to lose. Write the WHERE clause first, run it as a SELECT, look at the rows that come back, and only then change the word SELECT into the UPDATE. It takes five seconds. It is the single most valuable working habit in this entire course.

One more detail that confuses beginners: MySQL reports both "rows matched" and "rows changed", and they are often different. If a row already holds the value you are setting, it is matched but not changed. So Rows matched: 5 Changed: 0 does not mean your WHERE failed — it means those five rows were already correct.

-- Step 1: see exactly which rows the filter selects
SELECT id, name, marks FROM students WHERE email = 'ananya@example.com';

-- Step 2: same filter, now as an update
UPDATE students
SET marks = 93
WHERE email = 'ananya@example.com';

-- Several columns in one statement, separated by commas
UPDATE students
SET branch = 'IT',
    marks  = 90,
    is_active = 1
WHERE id = 4;

-- Step 3: confirm
SELECT id, name, branch, marks FROM students WHERE id = 4;

-- DANGER: no WHERE clause. Every student in the table gets 100.
-- UPDATE students SET marks = 100;

Guard Rails Worth Turning On

The SELECT-first habit is your main protection, but the tools offer more. MySQL has a setting called safe updates which refuses any UPDATE or DELETE whose WHERE clause does not use a key column. MySQL Workbench switches it on by default, which is why beginners sometimes see "You are using safe update mode" and reach for the internet to turn it off. Do not turn it off — that message is the tool stopping you from doing exactly the thing this lesson is warning about.

The stronger guard rail is a transaction. Run START TRANSACTION, run your update, check the result with a SELECT, and then either COMMIT to keep it or ROLLBACK to undo it as though it never happened. This works because MySQL's default storage engine, InnoDB, is transactional. It gives you a genuine undo button for the thirty seconds when you most need one.

By default MySQL runs in autocommit mode, meaning each statement commits by itself the instant it finishes. START TRANSACTION suspends that until you commit or roll back. Be aware that DDL statements such as CREATE TABLE or DROP TABLE cause an implicit commit in MySQL, so mixing them into a transaction does not protect them.

• Run the SELECT with the same WHERE first — always
• Leave MySQL's safe-update mode on while you are learning
• Wrap risky changes in START TRANSACTION so ROLLBACK is available
• Take a mysqldump before any bulk change on data you care about
• Add LIMIT to an update when you know how many rows should be affected (MySQL supports this)

-- A transaction as an undo button
START TRANSACTION;

UPDATE students SET branch = 'IT' WHERE branch = 'CSE';

-- Look before you leap: this SELECT sees the uncommitted change
SELECT branch, COUNT(*) FROM students GROUP BY branch;

-- Wrong? Undo it completely.
ROLLBACK;

-- Right? Make it permanent.
-- COMMIT;

-- MySQL's safe-update setting, per connection
SET SQL_SAFE_UPDATES = 1;   -- refuse UPDATE/DELETE without a key in WHERE

-- MySQL also allows a cap on how many rows an update may touch
UPDATE students SET is_active = 0 WHERE marks < 35 LIMIT 100;

Updating a Column From Its Own Value

The right-hand side of SET is an expression, and it can refer to the column's current value. That is how you increase a price by ten per cent, add five grace marks, or decrease stock after a sale — all without reading the old value into your application first.

Doing the arithmetic inside the database is not just tidier, it is safer. If you read stock into your program, subtract one, and write it back, another order processed in between will be silently overwritten. SET stock = stock - 1 is computed by the database on the current value, so two simultaneous sales both take effect.

There is a genuine dialect difference hiding here. When one SET assignment refers to a column another assignment has just changed, MySQL evaluates the assignments left to right and the second one sees the new value. PostgreSQL and the SQL standard evaluate every assignment against the row as it was before the statement. So a statement that swaps or chains two columns produces different results on the two engines. If you find yourself writing one, split it into two statements instead — that is unambiguous everywhere.

-- Arithmetic on the existing value
UPDATE products SET price = price * 1.10 WHERE category_id = 4;

-- Safe stock decrement, plus a guard so it cannot go negative
UPDATE products
SET stock = stock - 1
WHERE id = 7 AND stock > 0;

-- Grace marks, capped at 100 using LEAST
UPDATE students
SET marks = LEAST(marks + 5, 100)
WHERE marks IS NOT NULL;

-- Text can be built from itself too (MySQL)
UPDATE students
SET name = CONCAT(name, ' (alumni)')
WHERE joined_on < '2022-01-01';

-- Chained assignments behave differently across databases - avoid this
-- UPDATE products SET price = price * 2, old_price = price;

Different Values for Different Rows: CASE

Sometimes each group of rows needs a different new value. The naive approach is one UPDATE per group, which is several round trips and several separate passes over the table. A CASE expression does the whole thing in one statement.

CASE is SQL's if-else chain. It tests each WHEN in order, uses the first that matches, and falls back to ELSE if none do. It is an expression, not a statement, so it can appear anywhere a value can — in SET, in SELECT, even inside ORDER BY as the previous lesson showed.

One point catches people out. If there is no ELSE and no branch matches, CASE produces NULL — so an UPDATE using it will happily wipe a column for the rows you forgot to cover. Either write an ELSE, or restrict the statement with a WHERE clause so the uncovered rows are never touched. The example below does both.

-- One pass, three outcomes
UPDATE students
SET grade = CASE
        WHEN marks >= 85 THEN 'A'
        WHEN marks >= 70 THEN 'B'
        WHEN marks >= 50 THEN 'C'
        ELSE 'D'
    END
WHERE marks IS NOT NULL;      -- unmarked students are left alone

-- CASE also works in SELECT, which is how you preview the result first
SELECT
    name,
    marks,
    CASE
        WHEN marks >= 85 THEN 'A'
        WHEN marks >= 70 THEN 'B'
        WHEN marks >= 50 THEN 'C'
        ELSE 'D'
    END AS proposed_grade
FROM students
WHERE marks IS NOT NULL;

-- Missing ELSE: rows below 50 would have grade set to NULL
-- UPDATE students SET grade = CASE WHEN marks >= 50 THEN 'PASS' END;

Updating One Table From Another

A common real task is copying or computing values across tables — writing each order's total from its line items, or filling a student's course fee from a fees table. There are three ways to express it and they are not equally portable.

MySQL lets you join directly in the UPDATE. PostgreSQL and SQL Server use an UPDATE ... FROM form instead. The portable option, which works on all of them, is a correlated subquery in the SET clause. Use whichever suits your database, but recognise all three when you read code.

The correlated-subquery form has a sharp edge worth stating plainly. If the subquery finds no matching row, it returns NULL — and the update writes that NULL over whatever was there before. Add a WHERE EXISTS so that rows with no match are skipped rather than blanked.

MySQL adds one further restriction that produces a confusing error: you cannot update a table while selecting from that same table in a subquery in the WHERE clause. It reports "You can't specify target table for update in FROM clause". The workaround is to wrap the subquery in an extra SELECT, which forces MySQL to materialise it as a temporary table first.

-- MySQL: join inside the UPDATE
UPDATE orders o
JOIN (
    SELECT order_id, SUM(quantity * unit_price) AS computed
    FROM order_items
    GROUP BY order_id
) t ON t.order_id = o.id
SET o.total_amount = t.computed;

-- PostgreSQL and SQL Server use UPDATE ... FROM
-- UPDATE orders o
-- SET total_amount = t.computed
-- FROM (SELECT order_id, SUM(quantity * unit_price) AS computed
--       FROM order_items GROUP BY order_id) t
-- WHERE t.order_id = o.id;

-- Portable: a correlated subquery, guarded so non-matching rows are skipped
UPDATE orders o
SET total_amount = (
    SELECT SUM(quantity * unit_price)
    FROM order_items oi
    WHERE oi.order_id = o.id
)
WHERE EXISTS (
    SELECT 1 FROM order_items oi WHERE oi.order_id = o.id
);

-- MySQL error 1093 workaround: wrap the subquery one level deeper
UPDATE students
SET is_active = 0
WHERE id IN (
    SELECT id FROM (
        SELECT id FROM students WHERE marks < 35
    ) AS tmp
);