Practice: changing data
45 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)Changing data is where SQL mistakes cost the most, because a wrong UPDATE or DELETE does not fail: it succeeds on the wrong rows. Good habits are simple: know which rows match, predict the count, and check before and after.
Insert, update and delete tasks
The data-changing statements are INSERT (adds rows), UPDATE (changes rows) and DELETE (removes rows). Each names the table, says what to change and, for UPDATE and DELETE, says which rows with WHERE.
Example 1 takes one online order from start to finish with all three statements.
Example 1 · Online orders: one order from start to finish
CREATE TABLE web_orders ( web_order_id INT AUTO_INCREMENT PRIMARY KEY, customer VARCHAR(30) NOT NULL, item VARCHAR(30) NOT NULL, amount DECIMAL(8,2) NOT NULL, status ENUM('PLACED', 'SHIPPED', 'DELIVERED') NOT NULL DEFAULT 'PLACED');
INSERT INTO web_orders (customer, item, amount) VALUES ('Asha', 'Running Shoes', 2499.00), ('Ravi', 'Clean Code', 499.00), ('TEST', 'Test Item', 1.00);
UPDATE web_orders SET status = 'SHIPPED' WHERE web_order_id = 1;UPDATE web_orders SET status = 'DELIVERED' WHERE web_order_id = 1;DELETE FROM web_orders WHERE customer = 'TEST';
SELECT * FROM web_orders ORDER BY web_order_id;+--------------+----------+---------------+---------+-----------+ | web_order_id | customer | item | amount | status | +--------------+----------+---------------+---------+-----------+ | 1 | Asha | Running Shoes | 2499.00 | DELIVERED | | 2 | Ravi | Clean Code | 499.00 | PLACED | +--------------+----------+---------------+---------+-----------+
What this code does
- The
INSERTleaves outstatus, so every order starts asPLACED. TwoUPDATEs on the primary key move order 1 through its stages. - The
DELETEremoves the test order only. Ravi's order is untouched by all three changes because noWHEREmatched it.
Most real tasks combine all three. An order is an INSERT into orders, its id from LAST_INSERT_ID(), INSERTs into order_items and an UPDATE of stock. Foreign keys fix the order of work: parents before children when inserting, children before parents when deleting.
Predict how many rows change
Predicting the row count means working out how many rows a statement will change before you run it, then comparing your number with ROW_COUNT() or the rows affected line.
Example 2 predicts the number of changed rows for each statement, then compares it with ROW_COUNT().
Example 2 · Predict, then check: exam results
CREATE TABLE exam_results ( roll_no INT PRIMARY KEY, marks INT NULL, result VARCHAR(4) NULL);INSERT INTO exam_results VALUES (1, 72, NULL), (2, 35, NULL), (3, NULL, NULL), (4, 90, 'PASS');
UPDATE exam_results SET result = 'PASS' WHERE marks >= 40;SELECT ROW_COUNT() AS passed;
UPDATE exam_results SET result = 'FAIL' WHERE marks < 40;SELECT ROW_COUNT() AS failed;
DELETE FROM exam_results WHERE marks = NULL;SELECT ROW_COUNT() AS deleted;
SELECT * FROM exam_results ORDER BY roll_no;+--------+ | passed | +--------+ | 1 | +--------+ +--------+ | failed | +--------+ | 1 | +--------+ +---------+ | deleted | +---------+ | 0 | +---------+ +---------+-------+--------+ | roll_no | marks | result | +---------+-------+--------+ | 1 | 72 | PASS | | 2 | 35 | FAIL | | 3 | NULL | NULL | | 4 | 90 | PASS | +---------+-------+--------+
What this code does
- Rolls 1 and 4 match
marks >= 40, but roll 4 already saidPASS, so only 1 row changes. Roll 2 fails. Roll 3 hasNULLmarks and matches neither condition. marks = NULLis never true, so theDELETEremoves 0 rows. The Querying module shows the right test,IS NULL.
The rules from this module: a WHERE matches only rows where the condition is true, and NULL compared with =, < or > is never true. An UPDATE counts only rows whose value really changes. A multi-row INSERT that fails adds 0 rows. An upsert counts 1 per insert, 2 per update and 0 when nothing changes; REPLACE counts 2 per replaced row.
Fix dangerous statements
A dangerous statement runs without any error and still damages data, such as an UPDATE or DELETE with no WHERE, a WHERE on the wrong column or an INSERT IGNORE on real data.
Example 3 shows a stray semicolon that turns an UPDATE into a change of every row, caught by safe update mode.
Example 3 · A stray semicolon, caught by safe update mode
SET SQL_SAFE_UPDATES = 1;
UPDATE products SET price = price * 0.80;WHERE product_id = 3;ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
What this code does
- The
;after0.80ends theUPDATEbefore itsWHERE. Without safe mode, all 15 prices would drop by 20% and the next line would fail on its own with a syntax error. - Safe update mode refuses the
UPDATE, so nothing changes. The fix is to delete the stray;soWHERE product_id = 3is part of the statement.
Learn to spot them before you press Enter:
UPDATEorDELETEwith noWHERE, or with a stray;before theWHERE.- A
WHEREon the wrong column, such ascustomer_id = 3when you meantorder_id = 3. INSERTwithout a column list, where two values of the same type can swap silently.INSERT IGNOREon real data,REPLACEon a parent table, andTRUNCATEwhen you meant to remove a few rows. Safe update mode catches the first one. Your eyes have to catch the rest.
Find the bug
Find the dangerous line
Each statement runs without an error. One line is wrong.
Delete the items of order 4 only.
Round 1 of 2Mixed review: a safe change routine
A safe change routine is the four steps for every UPDATE and DELETE: SELECT the exact rows and count them, copy the WHERE into the change, check ROW_COUNT(), then SELECT again to see the result.
Example 4 corrects the price of the Fashion items using the four steps in order.
Example 4 · The safe routine: correct the price of Fashion items
-- 1. Which rows, and how many?SELECT product_id, name, price FROM products WHERE category_id = 6 ORDER BY product_id;
-- 2. The change with the same WHERE, 3. its row countUPDATE products SET price = price + 100.00 WHERE category_id = 6;SELECT ROW_COUNT() AS changed;
-- 4. Look at the resultSELECT product_id, name, price FROM products WHERE category_id = 6 ORDER BY product_id;+------------+---------------+---------+ | product_id | name | price | +------------+---------------+---------+ | 12 | Cotton Kurta | 899.00 | | 13 | Running Shoes | 2499.00 | +------------+---------------+---------+ +---------+ | changed | +---------+ | 2 | +---------+ +------------+---------------+---------+ | product_id | name | price | +------------+---------------+---------+ | 12 | Cotton Kurta | 999.00 | | 13 | Running Shoes | 2599.00 | +------------+---------------+---------+
What this code does
- Step 1 shows two Fashion products, so we expect 2 changed rows, and step 3 confirms exactly 2.
- If step 3 had shown a different number, you would stop and investigate before changing anything else.
Copy the WHERE into the change instead of retyping it, so the change hits exactly the rows you counted. ROW_COUNT() should then equal your count, or be lower for an UPDATE where some rows already had the value.
The Transactions module adds one more step: do all of this inside a transaction, so a surprise can still be undone with ROLLBACK.
Next step
Check your understanding
12 quick questions