Database NotesRohit’s interview study guide
/
Chapter 06

Changing Data

Writing to tables safely: INSERT, INSERT … SELECT, UPDATE and DELETE (including multi-table forms), DELETE vs TRUNCATE vs DROP, upserts with ON DUPLICATE KEY UPDATE, INSERT IGNORE and REPLACE, fast bulk loading, and the habits that stop a missing WHERE from ruining your day.

9 topics
Parts marked Advanced are extra depth. Skip them on a first read or a quick revision.

#INSERT

Added

INSERT adds new rows to a table. You name the table, optionally the columns you're supplying, and one or more rows of values. Every column you leave out gets its DEFAULT (or NULL, or the next AUTO_INCREMENT number); every constraint — NOT NULL, UNIQUE, foreign keys, CHECK, column types — is checked before the row goes in.

A good mental model is filling in a form that the table designed. The column list is the set of boxes you're filling in; boxes you skip get the form's pre-printed default; and the clerk (InnoDB in strict mode) refuses the whole form if a required box is empty or a value doesn't fit.

SQL
INSERT INTO customers (name, email, city, created_at)
VALUES ('Ava', 'ava@example.com', 'Paris', '2025-05-01 10:00:00'),
       ('Kenji', 'kenji@example.com', NULL, '2025-05-02 11:30:00');

SELECT LAST_INSERT_ID();

SELECT id, name, email, city FROM customers WHERE id >= 6;
Result
Query OK, 2 rows affected (0.003 sec)
Records: 2  Duplicates: 0  Warnings: 0

+------------------+
| LAST_INSERT_ID() |
+------------------+
|                7 |
+------------------+
1 row in set (0.000 sec)

+----+-------+-------------------+-------+
| id | name  | email             | city  |
+----+-------+-------------------+-------+
|  6 | Zara  | zara@example.com  | Dubai |
|  7 | Ava   | ava@example.com   | Paris |
|  8 | Kenji | kenji@example.com | NULL  |
+----+-------+-------------------+-------+
3 rows in set (0.000 sec)
What’s happening
  1. The column list (name, email, city, created_at) leaves out id, so InnoDB assigns the next AUTO_INCREMENT values. The sample data ends at customer 6 (Zara), so Ava gets 7 and Kenji 8.
  2. One statement inserts two rows: Query OK, 2 rows affected and Records: 2 Duplicates: 0 Warnings: 0. A multi-row INSERT is atomic — if the second row had failed, neither would be inserted.
  3. LAST_INSERT_ID() returns 7: for a multi-row insert it's the id of the first row inserted, not the last. It's per connection, so another client's inserts don't change your value.
  4. Kenji's city is explicitly NULL, which is allowed because the column is nullable. Leaving city out of the column list would have done the same, since its default is NULL.
  5. Always write the column list. INSERT INTO customers VALUES (…) without it depends on the table's column order and breaks the day someone adds a column.
AdvancedStrict mode errors on bad data

What strict mode rejects

MySQL 9.7 runs with STRICT_TRANS_TABLES in the default sql_mode, so bad data is an error, not a silent adjustment. Four common failures:

SQL
INSERT INTO customers (name, city, created_at)
VALUES ('NoEmail', 'Oslo', '2025-05-03 09:00:00');

INSERT INTO customers (name, email, created_at)
VALUES (REPEAT('x', 60), 'long@example.com', '2025-05-03 09:00:00');

INSERT INTO orders (customer_id, status, ordered_at)
VALUES (1, 'refunded', '2025-05-03 09:00:00');

INSERT INTO orders (customer_id, status, ordered_at)
VALUES (99, 'paid', '2025-05-03 09:00:00');
Result
ERROR 1364 (HY000): Field 'email' doesn't have a default value
ERROR 1406 (22001): Data too long for column 'name' at row 1
ERROR 1265 (01000): Data truncated for column 'status' at row 1
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ch04`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))
What’s happening
  1. email is NOT NULL with no default and wasn't supplied: ERROR 1364 (HY000): Field 'email' doesn't have a default value.
  2. name is VARCHAR(50) and we sent 60 characters: ERROR 1406 (22001): Data too long for column 'name' at row 1. Without strict mode MySQL would have cut it to 50 characters and only warned.
  3. 'refunded' isn't one of the ENUM values: ERROR 1265 (01000): Data truncated for column 'status' at row 1 — a confusing message for "invalid enum value". In non-strict mode the row would be stored with status = ''.
  4. Customer 99 doesn't exist, so the foreign key on orders.customer_id rejects it with ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails …. Foreign keys are enforced regardless of sql_mode.
  5. Every one of these would have been a quiet data-quality bug on a MySQL 5.6 server (strict mode became the default in 5.7). If you inherit an old server, check SELECT @@sql_mode.
AdvancedDuplicate keys and AUTO_INCREMENT gaps

Duplicates and AUTO_INCREMENT gaps

A UNIQUE or primary key violation is ERROR 1062. Note what it does to the id sequence:

SQL
INSERT INTO customers (name, email, created_at)
VALUES ('Rohit again', 'rohit@example.com', '2025-05-04 08:00:00');

INSERT INTO customers (name, email, created_at)
VALUES ('Lena', 'lena@example.com', '2025-05-04 08:05:00');

SELECT id, name FROM customers WHERE id >= 6;
Result
ERROR 1062 (23000): Duplicate entry 'rohit@example.com' for key 'customers.email'
Query OK, 1 row affected (0.002 sec)

+----+-------+
| id | name  |
+----+-------+
|  6 | Zara  |
|  7 | Ava   |
|  8 | Kenji |
| 10 | Lena  |
+----+-------+
4 rows in set (0.000 sec)
What’s happening
  1. rohit@example.com already exists, so the first insert fails: ERROR 1062 (23000): Duplicate entry 'rohit@example.com' for key 'customers.email'. The key name tells you which unique index was hit.
  2. Lena is inserted next. Ava and Kenji (7 and 8) came from the first example in this topic, so you might expect Lena to be 9 — but she's 10.
  3. InnoDB handed out 9 to the failed insert before the unique check rejected it, and a failed insert doesn't give its value back, so the sequence skipped.
  4. Gaps also come from rolled-back transactions, INSERT … ON DUPLICATE KEY UPDATE and bulk inserts that over-reserve. AUTO_INCREMENT promises unique, increasing ids — never gap-free ones. Don't use it for invoice numbers that must be consecutive.
AdvancedINSERT … SET, DEFAULT and no RETURNING

Other INSERT forms

SQL
INSERT INTO categories SET id = 6, name = 'Tablets', parent_id = 1;

INSERT INTO products (name, category_id, price)
VALUES ('Pixel Tablet', 6, 499.00);

SELECT id, name, category_id, price, stock FROM products WHERE category_id = 6;
Result
Query OK, 1 row affected (0.003 sec)

Query OK, 1 row affected (0.002 sec)

+----+--------------+-------------+--------+-------+
| id | name         | category_id | price  | stock |
+----+--------------+-------------+--------+-------+
|  9 | Pixel Tablet |           6 | 499.00 |     0 |
+----+--------------+-------------+--------+-------+
1 row in set (0.000 sec)
What’s happening
  1. INSERT … SET col = value, … is MySQL's alternative syntax for a single row; it reads like an UPDATE and is easy to keep in sync with the column names. It's MySQL-only.
  2. The second insert leaves out stock, so the column's DEFAULT 0 applies. You can also write the keyword DEFAULT in a VALUES list to ask for it explicitly.
  3. The new product gets the next id after the eight sample products: 9.
  4. MySQL has no INSERT … RETURNING (PostgreSQL and MariaDB do). In MySQL you read LAST_INSERT_ID() (or the driver's insertId) and query again if you need defaults or generated columns.
Predict the result
SQL
INSERT INTO departments (id, name) VALUES (5, 'Legal'), (6, 'Sales'), (7, 'Design');
SELECT COUNT(*) FROM departments;
Show answer

The insert fails and COUNT(*) is still 4.

SQL
INSERT INTO departments (id, name) VALUES (5, 'Legal'), (6, 'Sales'), (7, 'Design');
SELECT COUNT(*) FROM departments;
Result
ERROR 1062 (23000): Duplicate entry 'Sales' for key 'departments.name'
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.000 sec)
What’s happening
  1. departments.name is UNIQUE, and 'Sales' already exists (department 2).
  2. The second row fails with ERROR 1062 (23000): Duplicate entry 'Sales' for key 'departments.name'.
  3. InnoDB rolls back the whole statement, including the Legal row that was already written, so no rows are added.
  4. The count stays at the original four. To insert the good rows and skip the bad one you'd use INSERT IGNORE (see INSERT IGNORE and REPLACE) — with its own risks.

#INSERT … SELECT

Added

INSERT … SELECT inserts the result of a query instead of a literal VALUES list. It's how you copy rows between tables, archive old data, fill a summary table, or back-fill a new table from existing ones — all inside the server, without shipping the rows to your application and back.

Think of it as a pipe: the SELECT produces rows, and the INSERT pours them into the target table, matching columns by position (not by name). Everything you can do in a SELECT — joins, WHERE, GROUP BY, expressions — is available to shape the rows first.

SQL
CREATE TABLE orders_archive (
  id          INT PRIMARY KEY,
  customer_id INT NOT NULL,
  status      VARCHAR(20) NOT NULL,
  ordered_at  DATETIME NOT NULL,
  total       DECIMAL(10,2) NOT NULL,
  archived_at DATETIME NOT NULL
);

INSERT INTO orders_archive (id, customer_id, status, ordered_at, total, archived_at)
SELECT o.id, o.customer_id, o.status, o.ordered_at,
       SUM(i.quantity * i.unit_price),
       '2025-06-01 00:00:00'
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status IN ('shipped', 'cancelled')
  AND o.ordered_at < '2025-03-01'
GROUP BY o.id, o.customer_id, o.status, o.ordered_at;

SELECT * FROM orders_archive;
Result
Query OK, 0 rows affected (0.010 sec)

Query OK, 3 rows affected (0.002 sec)
Records: 3  Duplicates: 0  Warnings: 0

+----+-------------+-----------+---------------------+---------+---------------------+
| id | customer_id | status    | ordered_at          | total   | archived_at         |
+----+-------------+-----------+---------------------+---------+---------------------+
|  1 |           1 | shipped   | 2025-02-01 10:00:00 |  957.00 | 2025-06-01 00:00:00 |
|  3 |           2 | shipped   | 2025-02-10 09:20:00 | 1048.00 | 2025-06-01 00:00:00 |
|  4 |           3 | cancelled | 2025-02-14 18:00:00 | 1299.00 | 2025-06-01 00:00:00 |
+----+-------------+-----------+---------------------+---------+---------------------+
3 rows in set (0.000 sec)
What’s happening
  1. orders_archive is a new table with the order columns plus a computed total and an archived_at timestamp.
  2. The SELECT picks orders that are finished (shipped or cancelled) and placed before March: orders 1, 3 and 4. It joins their items and groups so each order becomes one row with its total.
  3. Its six columns are poured into the six listed target columns in order: o.id → id, …, SUM(…) → total, the literal → archived_at. Names in the SELECT don't matter; positions do.
  4. Records: 3 Duplicates: 0 Warnings: 0 — three rows copied. The ENUM status is converted to the VARCHAR column's string.
  5. In a real archive job you'd now delete the originals, inside the same transaction so an order can't end up in both places or in neither (see the next topics).
AdvancedCREATE TABLE … LIKE vs AS SELECT

Copying a table: CREATE TABLE … LIKE vs AS SELECT

Two shortcuts that people mix up:

SQL
CREATE TABLE customers_copy LIKE customers;
INSERT INTO customers_copy SELECT * FROM customers WHERE city = 'Toronto';

CREATE TABLE customers_snapshot AS SELECT id, name, city FROM customers;

SHOW CREATE TABLE customers_snapshot;
SELECT COUNT(*) FROM customers_copy;
Result
Query OK, 0 rows affected (0.013 sec)

Query OK, 2 rows affected (0.002 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 6 rows affected (0.010 sec)
Records: 6  Duplicates: 0  Warnings: 0

+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table              | Create Table                                                                                                                                                                                             |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customers_snapshot | CREATE TABLE `customers_snapshot` (
  `id` int NOT NULL DEFAULT '0',
  `name` varchar(50) NOT NULL,
  `city` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.000 sec)
What’s happening
  1. CREATE TABLE … LIKE copies the structure: columns, types, indexes, primary key, AUTO_INCREMENT, UNIQUE on email. It does not copy rows or foreign keys.
  2. INSERT INTO customers_copy SELECT * FROM customers WHERE … then copies the rows; SELECT * is acceptable here because both tables have identical column order by construction. Two Toronto customers are copied.
  3. CREATE TABLE … AS SELECT creates a table from the query's result shape: the column types come along, but SHOW CREATE TABLE shows no primary key, no indexes and no AUTO_INCREMENT (id is just int NOT NULL DEFAULT '0').
  4. Rule of thumb: LIKE + INSERT … SELECT for a working copy of a table; AS SELECT for a quick throwaway result.

#UPDATE

Added

UPDATE changes column values in existing rows. It has three parts: which table, SET with the new values (which can use the row's current values: salary = salary * 1.05), and WHERE to choose the rows. Leave out the WHERE and every row in the table is changed.

The mental model is find, then change: MySQL first finds the rows matching WHERE (using an index if it can, exactly as a SELECT would), locks them, and then applies the SET expressions to each row. That's why the safest habit is to write the WHERE as a SELECT first, check the rows, and only then turn it into an UPDATE.

SQL
SELECT id, name, salary FROM employees WHERE department_id = 2;

UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 2;

SELECT id, name, salary FROM employees WHERE department_id = 2;
Result
+----+-------+-----------+
| id | name  | salary    |
+----+-------+-----------+
|  5 | Elena | 130000.00 |
|  6 | Farid |  95000.00 |
|  7 | Grace |  99000.00 |
+----+-------+-----------+
3 rows in set (0.000 sec)

Query OK, 3 rows affected (0.002 sec)
Rows matched: 3  Changed: 3  Warnings: 0

+----+-------+-----------+
| id | name  | salary    |
+----+-------+-----------+
|  5 | Elena | 136500.00 |
|  6 | Farid |  99750.00 |
|  7 | Grace | 103950.00 |
+----+-------+-----------+
3 rows in set (0.000 sec)
What’s happening
  1. The first SELECT previews the rows the WHERE will touch: Elena (130000), Farid (95000) and Grace (99000).
  2. The UPDATE finds the same three rows (via the index on department_id) and computes salary * 1.05 from each row's current value.
  3. MySQL reports Rows matched: 3 Changed: 3 Warnings: 0. "Matched" is how many rows met the WHERE; "changed" is how many actually got a different value.
  4. Elena goes 130000 → 136500, Farid 95000 → 99750, Grace 99000 → 103950. DECIMAL arithmetic is exact, so no rounding surprises.
AdvancedRows matched vs rows changed

Matched vs changed

If the new value equals the old one, InnoDB doesn't rewrite the row, and the "affected rows" count drivers return is the changed count:

SQL
UPDATE customers SET city = 'Toronto' WHERE city = 'Toronto';
UPDATE customers SET city = 'Toronto' WHERE id IN (1, 4);
SELECT ROW_COUNT();
Result
Query OK, 0 rows affected (0.000 sec)
Rows matched: 2  Changed: 0  Warnings: 0

Query OK, 1 row affected (0.002 sec)
Rows matched: 2  Changed: 1  Warnings: 0

+-------------+
| ROW_COUNT() |
+-------------+
|           1 |
+-------------+
1 row in set (0.000 sec)
What’s happening
  1. The first statement matches Rohit and Noah (both already in Toronto) and changes nothing: Rows matched: 2 Changed: 0, Query OK, 0 rows affected.
  2. The second matches Rohit (no change) and Sofia (NULL → 'Toronto'): matched 2, changed 1.
  3. ROW_COUNT() returns the affected-rows count of the previous statement: 1.
  4. This trips up code that does if (result.affectedRows === 0) throw new Error('not found') — a no-op update of an existing row also reports 0. Node's mysql2 exposes both affectedRows and changedRows; or connect with the CLIENT_FOUND_ROWS flag to get matched counts.
AdvancedQuirk: SET runs left to right

Assignments run left to right (a MySQL quirk)

In MySQL, a single-table UPDATE evaluates SET assignments from left to right, and later ones see earlier results. The SQL standard (and PostgreSQL) evaluate every right-hand side against the old row. Shifting a value into a "previous" column shows the difference:

SQL
CREATE TABLE counters (id INT PRIMARY KEY, current INT, previous INT);
INSERT INTO counters VALUES (1, 10, NULL), (2, 10, NULL);

UPDATE counters SET current = current + 1, previous = current WHERE id = 1;
UPDATE counters SET previous = current, current = current + 1 WHERE id = 2;

SELECT * FROM counters;
Result
Query OK, 0 rows affected (0.012 sec)

Query OK, 2 rows affected (0.002 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.003 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+----+---------+----------+
| id | current | previous |
+----+---------+----------+
|  1 |      11 |       11 |
|  2 |      11 |       10 |
+----+---------+----------+
2 rows in set (0.000 sec)
What’s happening
  1. Both rows start with current = 10, previous = NULL, and both updates try to "remember the old value and increment".
  2. Row 1: current = current + 1 runs first, making current 11; then previous = current reads the new value: previous = 11. The old 10 is lost.
  3. Row 2: previous = current runs first and saves 10; then current becomes 11. Correct.
  4. In PostgreSQL both orders give previous = 10, because every expression sees the row as it was before the update. Code ported from PostgreSQL to MySQL can silently change meaning — put "save the old value" assignments first.

Here's PostgreSQL 18 running the "wrong" order:

PostgreSQL 18
CREATE TEMP TABLE counters (id INT PRIMARY KEY, current INT, previous INT);
INSERT INTO counters VALUES (1, 10, NULL);
UPDATE counters SET current = current + 1, previous = current WHERE id = 1;
SELECT * FROM counters;
Result
CREATE TABLE
INSERT 0 1
UPDATE 1
 id | current | previous 
----+---------+----------
  1 |      11 |       10
(1 row)
What’s happening
  1. Same statement as MySQL's row 1.
  2. PostgreSQL evaluates current + 1 and current both against the original row (current = 10), then assigns: current = 11, previous = 10.
  3. That's the standard behaviour. MySQL's left-to-right rule only applies to single-table updates; the manual warns that multi-table UPDATE doesn't guarantee any assignment order.
AdvancedUPDATE with ORDER BY and LIMIT

A single-table UPDATE (and DELETE) in MySQL can take ORDER BY and LIMIT, which is useful for "claim the oldest N" queues and for batching:

SQL
UPDATE orders
SET status = 'paid'
WHERE status = 'pending'
ORDER BY ordered_at
LIMIT 1;

SELECT id, status FROM orders WHERE id = 6;
Result
Query OK, 1 row affected (0.003 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+----+--------+
| id | status |
+----+--------+
|  6 | paid   |
+----+--------+
1 row in set (0.000 sec)
What’s happening
  1. WHERE status = 'pending' finds the pending orders (only order 6 in the sample data).
  2. ORDER BY ordered_at LIMIT 1 takes the oldest of them and updates only that one.
  3. Order 6 is now paid. In a job queue you'd combine this with SELECT … FOR UPDATE SKIP LOCKED so several workers don't claim the same row.
  4. Without ORDER BY, LIMIT updates whichever rows MySQL finds first — not deterministic, and statement-based replication warns about it as unsafe.
AdvancedError 1093: subquery on the target table

You can't read the table you're updating in a subquery

SQL
UPDATE employees
SET salary = (SELECT MAX(salary) FROM employees)
WHERE id = 9;
Result
ERROR 1093 (HY000): You can't specify target table 'employees' for update in FROM clause
What’s happening
  1. The subquery reads employees while the UPDATE is modifying employees. MySQL refuses: ERROR 1093 (HY000): You can't specify target table 'employees' for update in FROM clause.
  2. The reason is consistency: the subquery's answer could change as rows are updated. PostgreSQL allows it (it reads a snapshot).
  3. Workarounds: wrap it in a derived table so it's materialised first — SET salary = (SELECT m FROM (SELECT MAX(salary) AS m FROM employees) AS t) — or join to a derived table, or compute the value first into a variable.

#UPDATE and DELETE with joins

Added

Often the rows to change are defined by another table: "reduce stock for the products in order 6", "delete items belonging to cancelled orders". MySQL supports joins directly in UPDATE and DELETE (multi-table syntax), so you don't need a correlated subquery for each row — and a multi-table UPDATE can even change columns in several tables at once.

Think of it as SELECT with a different ending: write the join and WHERE you'd use to find the rows, then replace SELECT … with UPDATE … SET or DELETE target_alias. The join decides which rows; the SET or delete list decides which tables are written.

SQL
SELECT p.id, p.name, p.stock, i.quantity
FROM products p
JOIN order_items i ON i.product_id = p.id
WHERE i.order_id = 7;

UPDATE products p
JOIN order_items i ON i.product_id = p.id
JOIN orders o      ON o.id = i.order_id
SET p.stock  = p.stock - i.quantity,
    o.status = 'shipped'
WHERE o.id = 7;

SELECT p.id, p.name, p.stock FROM products p WHERE p.id IN (1, 5);
SELECT id, status FROM orders WHERE id = 7;
Result
+----+---------------------------+-------+----------+
| id | name                      | stock | quantity |
+----+---------------------------+-------+----------+
|  1 | ThinkBook 14              |    12 |        1 |
|  5 | SQL Performance Explained |    50 |        3 |
+----+---------------------------+-------+----------+
2 rows in set (0.000 sec)

Query OK, 3 rows affected (0.003 sec)
Rows matched: 3  Changed: 3  Warnings: 0

+----+---------------------------+-------+
| id | name                      | stock |
+----+---------------------------+-------+
|  1 | ThinkBook 14              |    11 |
|  5 | SQL Performance Explained |    47 |
+----+---------------------------+-------+
2 rows in set (0.000 sec)

+----+---------+
| id | status  |
+----+---------+
|  7 | shipped |
+----+---------+
1 row in set (0.000 sec)
What’s happening
  1. The preview SELECT shows order 7's lines: one ThinkBook 14 (stock 12) and three copies of SQL Performance Explained (stock 50).
  2. The UPDATE joins products to order lines to the order, and the WHERE o.id = 7 restricts it to that order's two lines.
  3. SET p.stock = p.stock - i.quantity uses each joined row's quantity: ThinkBook 12 → 11, SQL Performance Explained 50 → 47.
  4. o.status = 'shipped' updates the orders table in the same statement. Order 7 appears in two joined rows, but it's only changed once — the output reports Rows matched: 3 Changed: 3 (two products plus one order).
  5. Because both tables change in one statement, they can't get out of sync if the client crashes halfway. (You'd still wrap a real checkout in a transaction with other steps.)
AdvancedMulti-table DELETE and PostgreSQL USING

Multi-table DELETE

DELETE lists the aliases to delete from between DELETE and FROM; the other joined tables are only used to find the rows:

SQL
DELETE i
FROM order_items i
JOIN orders o ON o.id = i.order_id
WHERE o.status = 'cancelled';

SELECT order_id, product_id FROM order_items WHERE order_id = 4;
Result
Query OK, 1 row affected (0.003 sec)

Empty set (0.000 sec)
What’s happening
  1. The join finds order lines whose order is cancelled: only order 4's single line (the MacBook Air).
  2. DELETE i deletes from order_items only; orders is read but untouched. Query OK, 1 row affected.
  3. The check returns Empty set. Order 4 itself still exists.
  4. The PostgreSQL spelling is DELETE FROM order_items i USING orders o WHERE o.id = i.order_id AND o.status = 'cancelled', and UPDATE … FROM for updates. SQL Server uses UPDATE … FROM / DELETE … FROM … JOIN.
AdvancedPitfall: deleting parent and child together

Deleting parent and child together: the foreign-key trap

You can list several aliases to delete from. But with foreign keys, the order MySQL processes the tables in isn't guaranteed to be child-first:

SQL
DELETE o, i
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'cancelled';

SELECT COUNT(*) FROM orders WHERE status = 'cancelled';
Result
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ch04`.`order_items`, CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`))
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.000 sec)
What’s happening
  1. The statement wants to delete cancelled order 4 and its line in one go.
  2. MySQL tried to delete the orders row while its order_items child still existed, so InnoDB's foreign key check failed: ERROR 1451 (23000): Cannot delete or update a parent row ….
  3. The statement is rolled back as a whole, so order 4 is still there (COUNT(*) is 1).
  4. The manual warns about exactly this: for multi-table deletes on InnoDB tables with foreign keys, the optimizer may process tables in an order different from their parent/child relationship. Delete the children first in one statement and the parent in another (in a transaction), or define the foreign key with ON DELETE CASCADE.
AdvancedNo ORDER BY or LIMIT with joins

Restrictions

Multi-table UPDATE and DELETE can't use ORDER BY or LIMIT:

SQL
UPDATE products p
JOIN order_items i ON i.product_id = p.id
SET p.stock = p.stock - i.quantity
WHERE i.order_id = 6
ORDER BY p.id
LIMIT 1;
Result
ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY
What’s happening
  1. MySQL rejects it: ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY.
  2. If you need to limit a join-based update, select the target primary keys first (with ORDER BY … LIMIT in a derived table) and join to that: JOIN (SELECT id FROM … ORDER BY … LIMIT 100) AS batch ON batch.id = t.id.

#DELETE vs TRUNCATE vs DROP

Added

All three make data go away, at very different levels:

DELETE FROM t WHERE …TRUNCATE TABLE tDROP TABLE t
Removeschosen rows (or all)all rowsrows and the table definition
KindDML, row by rowDDL (drops and recreates the table)DDL
WHEREyesnono
Transactioncan be rolled backimplicit commit, can't be rolled backimplicit commit, can't be rolled back
TriggersON DELETE triggers firedon't firedon't fire
AUTO_INCREMENTkeeps countingreset to the startgone with the table
Foreign keyschecked per row (or cascade)refused if another table references itrefused if another table references it
Speed on big tablesslow (undo log, per-row locks)fastfast

The analogy: DELETE is erasing lines from a notebook one by one (you can undo each erasure until you commit); TRUNCATE is tearing out all the pages and putting in fresh blank ones; DROP is throwing the notebook away.

SQL
CREATE TABLE audit_log (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  message VARCHAR(100) NOT NULL
);
INSERT INTO audit_log (message) VALUES ('a'), ('b'), ('c');

START TRANSACTION;
DELETE FROM audit_log;
SELECT COUNT(*) AS after_delete FROM audit_log;
ROLLBACK;
SELECT COUNT(*) AS after_rollback FROM audit_log;

DELETE FROM audit_log;
INSERT INTO audit_log (message) VALUES ('d');
SELECT id, message FROM audit_log;

TRUNCATE TABLE audit_log;
INSERT INTO audit_log (message) VALUES ('e');
SELECT id, message FROM audit_log;
Result
Query OK, 0 rows affected (0.011 sec)

Query OK, 3 rows affected (0.003 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.000 sec)

Query OK, 3 rows affected (0.000 sec)

+--------------+
| after_delete |
+--------------+
|            0 |
+--------------+
1 row in set (0.000 sec)

Query OK, 0 rows affected (0.001 sec)

+----------------+
| after_rollback |
+----------------+
|              3 |
+----------------+
1 row in set (0.000 sec)

Query OK, 3 rows affected (0.002 sec)

Query OK, 1 row affected (0.002 sec)

+----+---------+
| id | message |
+----+---------+
|  4 | d       |
+----+---------+
1 row in set (0.000 sec)

Query OK, 0 rows affected (0.017 sec)

Query OK, 1 row affected (0.002 sec)

+----+---------+
| id | message |
+----+---------+
|  1 | e       |
+----+---------+
1 row in set (0.000 sec)
What’s happening
  1. Three rows go in with ids 1, 2 and 3.
  2. Inside a transaction, DELETE FROM audit_log removes all three (3 rows affected), and the count is 0 — but ROLLBACK brings them all back (after_rollback = 3). DELETE writes undo records for every row, which is what makes that possible (and what makes it slow on millions of rows).
  3. The second DELETE (autocommit) removes them for real. The next insert gets id 4: DELETE never resets AUTO_INCREMENT.
  4. TRUNCATE TABLE reports 0 rows affected (it doesn't count rows; it recreates the table) and resets the counter, so the next insert gets id 1.
  5. Use DELETE with a WHERE for normal data removal; TRUNCATE for wiping scratch, staging or test tables; DROP when the table itself should go.
AdvancedTRUNCATE commits your transaction

TRUNCATE (like every DDL statement) causes an implicit commit before it runs — so it commits whatever else your transaction had done:

SQL
CREATE TABLE staging (id INT PRIMARY KEY);
INSERT INTO staging VALUES (1), (2);

START TRANSACTION;
DELETE FROM customers WHERE id = 6;
TRUNCATE TABLE staging;
ROLLBACK;

SELECT COUNT(*) AS zara_rows FROM customers WHERE id = 6;
Result
Query OK, 0 rows affected (0.012 sec)

Query OK, 2 rows affected (0.002 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.000 sec)

Query OK, 1 row affected (0.000 sec)

Query OK, 0 rows affected (0.018 sec)

Query OK, 0 rows affected (0.000 sec)

+-----------+
| zara_rows |
+-----------+
|         0 |
+-----------+
1 row in set (0.000 sec)
What’s happening
  1. The transaction deletes Zara (customer 6, who has no orders, so no foreign key blocks it).
  2. TRUNCATE TABLE staging implicitly commits the open transaction before truncating — the delete of Zara is now permanent.
  3. ROLLBACK has nothing left to roll back. It still prints Query OK, with no warning.
  4. zara_rows is 0: Zara is gone. Never mix DDL (TRUNCATE, DROP, ALTER, CREATE) into a transaction you might want to roll back; MySQL has no transactional DDL (PostgreSQL does).
AdvancedForeign keys block TRUNCATE and DROP
SQL
TRUNCATE TABLE orders;
DROP TABLE customers;
DELETE FROM orders WHERE id = 4;
Result
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (`ch04`.`order_items`, CONSTRAINT `order_items_ibfk_1`)
ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'orders_ibfk_1' on table 'orders'.
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ch04`.`order_items`, CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`))
What’s happening
  1. orders is referenced by order_items.order_id, so TRUNCATE is refused outright, even though deleting all orders would also mean deleting their items: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint ….
  2. customers is referenced by orders.customer_id, so DROP TABLE customers fails with ERROR 3730 (HY000), naming the constraint and the referencing table.
  3. A plain DELETE of order 4 is checked row by row; order 4 still has an item, so it fails with ERROR 1451 (23000) (the foreign key has no ON DELETE CASCADE).
  4. To empty a parent table you delete children first, or drop/disable the constraint. SET FOREIGN_KEY_CHECKS = 0 makes TRUNCATE and DROP work, but it also lets you leave orphaned rows behind, so it's for controlled maintenance scripts only.

#Upserts: ON DUPLICATE KEY UPDATE

Added

An upsert is "insert this row, or if it already exists, update it". Doing it in two statements (SELECT to check, then INSERT or UPDATE) has a race: two clients both see "not there" and both insert, and one hits a duplicate-key error. MySQL's INSERT … ON DUPLICATE KEY UPDATE does it atomically in one statement: try the insert; if it would violate a PRIMARY KEY or UNIQUE index, update the existing row instead.

Think of a guest book with one line per guest. You try to write a new line; if the guest's name is already there, you instead add a tick to their existing line. The "name" is whatever unique key the table has — the upsert only works if the table has a unique key that identifies "the same row".

SQL
CREATE TABLE product_views (
  product_id  INT PRIMARY KEY,
  views       INT NOT NULL,
  last_viewed DATETIME NOT NULL
);

INSERT INTO product_views (product_id, views, last_viewed)
VALUES (3, 1, '2025-05-01 09:00:00') AS new
ON DUPLICATE KEY UPDATE views = product_views.views + new.views, last_viewed = new.last_viewed;

INSERT INTO product_views (product_id, views, last_viewed)
VALUES (3, 1, '2025-05-01 09:05:00') AS new
ON DUPLICATE KEY UPDATE views = product_views.views + new.views, last_viewed = new.last_viewed;

INSERT INTO product_views (product_id, views, last_viewed)
VALUES (3, 1, '2025-05-01 09:05:00') AS new
ON DUPLICATE KEY UPDATE last_viewed = new.last_viewed;

SELECT * FROM product_views;
Result
Query OK, 0 rows affected (0.013 sec)

Query OK, 1 row affected (0.002 sec)

Query OK, 2 rows affected (0.001 sec)

Query OK, 0 rows affected (0.000 sec)

+------------+-------+---------------------+
| product_id | views | last_viewed         |
+------------+-------+---------------------+
|          3 |     2 | 2025-05-01 09:05:00 |
+------------+-------+---------------------+
1 row in set (0.000 sec)
What’s happening
  1. First statement: product 3 has no row yet, so it's a plain insert with views = 1. MySQL reports 1 row affected.
  2. Second statement: the insert would duplicate primary key 3, so MySQL runs the UPDATE part on the existing row instead: views = product_views.views + new.views (1 + 1 = 2) and last_viewed = new.last_viewed. It reports 2 rows affected — that's MySQL's code for "an existing row was updated".
  3. AS new (MySQL 8.0.19+) names the row you tried to insert, so new.views means "the value from the VALUES list" and product_views.views the existing row's value. A bare views would now be ambiguous — we tried it, and MySQL refuses with ERROR 1052 (23000): Column 'views' in field list is ambiguous — so qualify both sides.
  4. Third statement: the update would set last_viewed to the value it already has, so nothing changes and MySQL reports 0 rows affected. The manual's rule: 1 = inserted, 2 = updated, 0 = existing row set to its current values.
  5. The final row: views = 2, last_viewed = 2025-05-01 09:05:00. Two clients running this concurrently can't both insert — the unique key serialises them.
AdvancedThe deprecated VALUES() function

The old VALUES() syntax

Before 8.0.19 you referred to the inserted value with the VALUES(col) function. It still works in 9.7, but it's deprecated (since 8.0.20) and warns:

SQL
INSERT INTO products (id, name, category_id, price, stock)
VALUES (7, 'USB-C Charger', 5, 25.00, 10)
ON DUPLICATE KEY UPDATE price = VALUES(price), stock = stock + VALUES(stock);

SHOW WARNINGS;
SELECT id, name, price, stock FROM products WHERE id = 7;
Result
Query OK, 2 rows affected, 2 warnings (0.002 sec)

+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                   |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead |
| Warning | 1287 | 'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

+----+---------------+-------+-------+
| id | name          | price | stock |
+----+---------------+-------+-------+
|  7 | USB-C Charger | 25.00 |   110 |
+----+---------------+-------+-------+
1 row in set (0.000 sec)
What’s happening
  1. Product 7 exists, so the update branch runs: price becomes the inserted 25.00 and stock goes 100 → 110. 2 rows affected, 2 warnings — one per VALUES() call.
  2. Each warning is 1287: 'VALUES function' is deprecated and will be removed in a future release, suggesting the AS alias form.
  3. You'll still see VALUES() everywhere in code written for MySQL 5.7 and early 8.0 (and in ORMs that target old versions). New code should use the row alias.
AdvancedMulti-row upserts and AUTO_INCREMENT gaps

Upserts work with many rows at once — a common pattern for syncing data. Upserting customers by their unique email:

SQL
INSERT INTO customers (name, email, city, created_at)
VALUES ('Rohit', 'rohit@example.com', 'Vancouver', '2025-05-10 10:00:00'),
       ('Iris',  'iris@example.com',  'Berlin',    '2025-05-10 10:00:00') AS new
ON DUPLICATE KEY UPDATE city = new.city;

INSERT INTO customers (name, email, created_at)
VALUES ('Omar', 'omar@example.com', '2025-05-10 10:05:00');

SELECT id, name, email, city FROM customers WHERE id = 1 OR id > 6;
Result
Query OK, 3 rows affected (0.004 sec)
Records: 2  Duplicates: 1  Warnings: 0

Query OK, 1 row affected (0.002 sec)

+----+-------+-------------------+-----------+
| id | name  | email             | city      |
+----+-------+-------------------+-----------+
|  1 | Rohit | rohit@example.com | Vancouver |
|  7 | Iris  | iris@example.com  | Berlin    |
|  9 | Omar  | omar@example.com  | NULL      |
+----+-------+-------------------+-----------+
3 rows in set (0.000 sec)
What’s happening
  1. Rohit's email already exists (customer 1), so his row is updated: city Toronto → Vancouver. Iris is new and is inserted. The statement reports 3 rows affected — 2 for the update plus 1 for the insert — and Duplicates: 1.
  2. The primary key id isn't in the column list, so the duplicate was detected on the UNIQUE index on email. Any unique index counts.
  3. Look at the ids: InnoDB reserved an AUTO_INCREMENT value for each row in the statement, including Rohit's row that turned into an update. So Iris and Omar don't get consecutive ids — the manual states the insert increases the counter even when the update path is taken.
  4. On a table that's upserted constantly, this can burn through an INT primary key surprisingly fast. Use BIGINT ids, or check first and only upsert when needed.
AdvancedPostgreSQL: ON CONFLICT … RETURNING

PostgreSQL: ON CONFLICT

PostgreSQL names the conflict target explicitly and calls the proposed row EXCLUDED:

PostgreSQL 18
BEGIN;
INSERT INTO customers (name, email, city, created_at)
VALUES ('Rohit', 'rohit@example.com', 'Vancouver', '2025-05-10 10:00:00'),
       ('Iris',  'iris@example.com',  'Berlin',    '2025-05-10 10:00:00')
ON CONFLICT (email) DO UPDATE SET city = EXCLUDED.city
RETURNING id, name, city, (xmax = 0) AS inserted;
ROLLBACK;
Result
BEGIN
 id | name  |   city    | inserted 
----+-------+-----------+----------
  1 | Rohit | Vancouver | f
  8 | Iris  | Berlin    | t
(2 rows)

INSERT 0 2
ROLLBACK
What’s happening
  1. ON CONFLICT (email) says which unique constraint triggers the update path — no ambiguity with several unique keys.
  2. EXCLUDED.city is the value from the row that couldn't be inserted, the equivalent of MySQL's new.city.
  3. RETURNING returns the final rows, and the xmax = 0 trick reports whether each row was freshly inserted (t for Iris) or updated (f for Rohit; it's a well-known hack relying on PostgreSQL internals, not a documented API). MySQL has no equivalent; you get only the affected-rows count. The block runs inside BEGIN … ROLLBACK, so the sample data is unchanged — though Iris's identity value is consumed, since sequences aren't transactional.
  4. PostgreSQL 15+ also has the standard MERGE statement; MySQL doesn't.

#INSERT IGNORE and REPLACE

Added

Two more MySQL-specific ways to deal with "the row might already exist", both blunter than ON DUPLICATE KEY UPDATE:

  • INSERT IGNORE — insert what you can; rows that would cause a duplicate-key error (and several other errors) are skipped with a warning instead of failing the statement.
  • REPLACE — if a row with the same primary or unique key exists, delete it, then insert the new row. It's not an update: it's a delete plus an insert.

Back to the guest book: INSERT IGNORE means "if the guest is already listed, don't write anything". REPLACE means "if the guest is already listed, rip out their line and write a brand-new one" — anything that was on the old line and isn't on the new one is lost.

SQL
INSERT IGNORE INTO customers (name, email, city, created_at)
VALUES ('Rohit dup', 'rohit@example.com', 'Paris',  '2025-05-01 10:00:00'),
       ('Mina',      'mina@example.com',  'Seoul',  '2025-05-01 10:00:00');

SHOW WARNINGS;
SELECT id, name, email, city FROM customers WHERE email IN ('rohit@example.com', 'mina@example.com');
Result
Query OK, 1 row affected, 1 warning (0.003 sec)
Records: 2  Duplicates: 1  Warnings: 1

+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1062 | Duplicate entry 'rohit@example.com' for key 'customers.email' |
+---------+------+---------------------------------------------------------------+
1 row in set (0.000 sec)

+----+-------+-------------------+---------+
| id | name  | email             | city    |
+----+-------+-------------------+---------+
|  7 | Mina  | mina@example.com  | Seoul   |
|  1 | Rohit | rohit@example.com | Toronto |
+----+-------+-------------------+---------+
2 rows in set (0.000 sec)
What’s happening
  1. The first row's email duplicates customer 1. With IGNORE, instead of ERROR 1062, MySQL skips that row and records a warning.
  2. The second row is inserted (Mina gets id 7). The status line reads 1 row affected, 1 warning and Records: 2 Duplicates: 1 Warnings: 1.
  3. SHOW WARNINGS shows the downgraded error: Warning | 1062 | Duplicate entry 'rohit@example.com' for key 'customers.email'.
  4. Rohit's existing row is unchanged — still Toronto. INSERT IGNORE never updates anything.
AdvancedWhat else IGNORE swallows

IGNORE doesn't only skip duplicates: it downgrades many errors to warnings, and some of those become silently "fixed" data:

SQL
INSERT IGNORE INTO orders (customer_id, status, ordered_at)
VALUES (99, 'paid',     '2025-05-01 10:00:00'),
       (1,  'refunded', '2025-05-01 10:00:00');

SHOW WARNINGS;
SELECT id, customer_id, status FROM orders WHERE id > 8;
Result
Query OK, 1 row affected, 2 warnings (0.003 sec)
Records: 1  Duplicates: 0  Warnings: 2

+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                  |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1452 | Cannot add or update a child row: a foreign key constraint fails (`ch04`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)) |
| Warning | 1265 | Data truncated for column 'status' at row 1                                                                                                                              |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

+----+-------------+--------+
| id | customer_id | status |
+----+-------------+--------+
|  9 |           1 |        |
+----+-------------+--------+
1 row in set (0.000 sec)
What’s happening
  1. The first row references customer 99, who doesn't exist. Normally that's ERROR 1452; with IGNORE the row is skipped with warning 1452.
  2. The second row has an invalid ENUM value. Normally ERROR 1265; with IGNORE it's a warning — and the row is inserted anyway, with status set to the empty string '' (the special "invalid enum" value).
  3. So the statement "succeeded" — 1 row affected, 2 warnings, and Records: 1 because the skipped row isn't even counted — with one bad row dropped and one stored with garbage in it. Most drivers don't show warnings, so nobody notices.
  4. Use INSERT IGNORE only when duplicates are the expected case and the rest of the data is trusted (for example, loading a deduplicated list of tags). For "insert unless it exists", INSERT … ON DUPLICATE KEY UPDATE id = id is a safer no-op upsert because it doesn't hide other errors.
AdvancedREPLACE: delete plus insert, and cascades

REPLACE is delete + insert

SQL
SELECT id, name, email, city, created_at FROM customers WHERE id = 6;

REPLACE INTO customers (id, name, email, created_at)
VALUES (6, 'Zara Khan', 'zara@example.com', '2025-05-20 12:00:00');

SELECT id, name, email, city, created_at FROM customers WHERE id = 6;

REPLACE INTO customers (name, email, created_at)
VALUES ('Rohit Varma', 'rohit@example.com', '2025-05-20 12:00:00');
Result
+----+------+------------------+-------+---------------------+
| id | name | email            | city  | created_at          |
+----+------+------------------+-------+---------------------+
|  6 | Zara | zara@example.com | Dubai | 2025-04-01 16:20:00 |
+----+------+------------------+-------+---------------------+
1 row in set (0.000 sec)

Query OK, 2 rows affected (0.003 sec)

+----+-----------+------------------+------+---------------------+
| id | name      | email            | city | created_at          |
+----+-----------+------------------+------+---------------------+
|  6 | Zara Khan | zara@example.com | NULL | 2025-05-20 12:00:00 |
+----+-----------+------------------+------+---------------------+
1 row in set (0.000 sec)

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ch04`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))
What’s happening
  1. Zara (id 6) lives in Dubai.
  2. REPLACE with id 6 conflicts with her row, so the old row is removed and the new one stored: 2 rows affected — the manual defines the count as rows deleted plus rows inserted.
  3. The new row has city = NULL: city wasn't in the column list, so it got its default, exactly as for INSERT. An UPDATE would have kept Dubai. Every column you don't supply is reset.
  4. The second REPLACE has no id, so it conflicts with Rohit's row on the UNIQUE email and would get a new AUTO_INCREMENT id. Deleting customer 1 would orphan his two orders, so the foreign key stops it: ERROR 1451 (23000).
  5. When the replacement keeps the same primary key (like Zara's), InnoDB may carry it out as an in-place update — the manual allows that — which is why a same-id REPLACE of a referenced row can succeed. Don't count on it to protect child rows; see the cascade example next.

With ON DELETE CASCADE, a REPLACE that changes the key silently deletes the children:

SQL
CREATE TABLE tags (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  slug  VARCHAR(30) NOT NULL UNIQUE,
  label VARCHAR(30)
);
CREATE TABLE post_tags (
  post_id INT NOT NULL,
  tag_id  INT NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);
INSERT INTO tags (slug, label) VALUES ('sql', 'SQL');
INSERT INTO post_tags VALUES (100, 1), (101, 1), (102, 1);

REPLACE INTO tags (slug, label) VALUES ('sql', 'Structured Query Language');

SELECT * FROM tags;
SELECT COUNT(*) AS posts_still_tagged FROM post_tags;
Result
Query OK, 0 rows affected (0.014 sec)

Query OK, 0 rows affected (0.013 sec)

Query OK, 1 row affected (0.002 sec)

Query OK, 3 rows affected (0.002 sec)
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 2 rows affected (0.003 sec)

+----+------+---------------------------+
| id | slug | label                     |
+----+------+---------------------------+
|  2 | sql  | Structured Query Language |
+----+------+---------------------------+
1 row in set (0.000 sec)

+--------------------+
| posts_still_tagged |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.000 sec)
What’s happening
  1. Tag sql gets id 1, and three posts are tagged with it.
  2. The REPLACE conflicts on the unique slug. It deletes tag 1 — which cascades to all three post_tags rows — and inserts a fresh row, which gets the next AUTO_INCREMENT id, 2.
  3. MySQL reports 2 rows affected, which looks harmless. The cascaded deletes aren't in that count.
  4. posts_still_tagged is 0. The intent was "rename the label"; the effect was "untag every post". INSERT … ON DUPLICATE KEY UPDATE label = new.label would have updated tag 1 in place and kept all three links.

#Bulk loading data

Added

Loading thousands or millions of rows one INSERT at a time is slow for a boring reason: every statement pays fixed costs — a network round trip, parsing, and (with autocommit) a transaction commit that flushes the redo log to disk. Bulk loading is about paying those costs once per batch instead of once per row.

The analogy is moving house. Carrying one item per trip in your car is limited by the trips, not the items. Packing boxes (multi-row inserts), making one trip for many boxes (one transaction), or hiring a removal lorry (LOAD DATA) all cut the number of trips.

We measured four ways of inserting 10,000 rows into a simple table on this sandbox (MySQL 9.7 in Docker on a Mac, client inside the container, so network cost is minimal; each test run twice):

Measured on this sandbox
method                                             10,000 rows    200,000 rows
-------------------------------------------------  -------------  -------------
A  one INSERT per row, autocommit (a commit each)   9.9 – 10.3 s   (not run)
B  one INSERT per row, inside one transaction       0.18 – 0.20 s  2.07 – 2.12 s
C  multi-row INSERT, 1,000 rows per statement       0.14 s         0.76 – 0.77 s
D  LOAD DATA INFILE from a CSV file                 0.11 – 0.12 s  0.35 – 0.47 s
(an empty round trip, for comparison: 0.07 – 0.10 s)
What’s happening
  1. A is fifty times slower than everything else: with autocommit, each of the 10,000 statements is its own transaction, and each commit waits for the redo log to be flushed to disk (innodb_flush_log_at_trx_commit = 1, the durable default). The disk sync dominates.
  2. B sends the same 10,000 statements but commits once, so there's only one flush. The remaining cost is parsing 10,000 statements.
  3. C sends 10 statements of 1,000 rows each: fewer parses, fewer round trips. At 200,000 rows it's almost 3× faster than B.
  4. D skips SQL parsing per row entirely: the server reads the file and builds rows directly. It's the fastest, around 2× faster than C at 200,000 rows.
  5. Over a real network, the round-trip cost per statement is much larger than inside this container, which makes A and B even worse relative to C and D.
AdvancedLOAD DATA INFILE from a CSV

LOAD DATA INFILE

LOAD DATA reads a delimited text file straight into a table. Here's a small CSV with a header row, a quoted field containing a comma, an empty stock, and one row with a category that doesn't exist:

new_products.csv
name,category_id,price,stock
"Kindle Paperwhite",5,169.99,15
"Clean Code",4,35.50,40
"USB-C Hub, 7-port",5,59.00,
"Mystery Gadget",9,10.00,1

The server only reads files from the directory in secure_file_priv, which in the official Docker image is /var/lib/mysql-files/:

SQL
SELECT @@secure_file_priv;

LOAD DATA INFILE '/var/lib/mysql-files/new_products.csv'
INTO TABLE products
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name, category_id, price, @stock)
SET stock = IF(@stock = '', 0, @stock);

SELECT COUNT(*) FROM products;
Result
+-----------------------+
| @@secure_file_priv    |
+-----------------------+
| /var/lib/mysql-files/ |
+-----------------------+
1 row in set (0.000 sec)

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`ch04`.`products`, CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`))
+----------+
| COUNT(*) |
+----------+
|        8 |
+----------+
1 row in set (0.000 sec)
What’s happening
  1. secure_file_priv restricts server-side file access to one directory, so a SQL injection can't read /etc/passwd. If it's NULL, server-side LOAD DATA INFILE is disabled entirely.
  2. FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' parses CSV: "USB-C Hub, 7-port" stays one field because of the quotes. IGNORE 1 LINES skips the header.
  3. The column list maps fields by position; @stock reads the fourth field into a user variable instead of a column, so SET stock = IF(@stock = '', 0, @stock) can turn the empty value into 0.
  4. The "Mystery Gadget" row references category 9, which doesn't exist: ERROR 1452 (23000) from the foreign key. LOAD DATA is one statement, so on InnoDB the whole load is rolled back — COUNT(*) is still 8.
  5. That's usually what you want from an import: all or nothing. If you'd rather keep the good rows, add IGNORE (next example) and inspect the warnings.
SQL
LOAD DATA INFILE '/var/lib/mysql-files/new_products.csv'
IGNORE INTO TABLE products
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name, category_id, price, @stock)
SET stock = IF(@stock = '', 0, @stock);

SHOW WARNINGS;
SELECT id, name, category_id, price, stock FROM products WHERE id > 8;
Result
Query OK, 3 rows affected, 1 warning (0.004 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 1

+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                       |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1452 | Cannot add or update a child row: a foreign key constraint fails (`ch04`.`products`, CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`)) |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

+----+-------------------+-------------+--------+-------+
| id | name              | category_id | price  | stock |
+----+-------------------+-------------+--------+-------+
| 12 | Kindle Paperwhite |           5 | 169.99 |    15 |
| 13 | Clean Code        |           4 |  35.50 |    40 |
| 14 | USB-C Hub, 7-port |           5 |  59.00 |     0 |
+----+-------------------+-------------+--------+-------+
3 rows in set (0.000 sec)
What’s happening
  1. IGNORE INTO TABLE (the first IGNORE, before INTO) turns row errors into warnings, just like INSERT IGNORE. The second IGNORE 1 LINES is unrelated: it skips the header.
  2. Three rows are loaded and the Mystery Gadget is dropped: Records: 3 Deleted: 0 Skipped: 0 Warnings: 1 (the row that failed isn't counted as a record), and SHOW WARNINGS shows the foreign-key failure as warning 1452.
  3. The hub's empty stock became 0 thanks to the SET clause, and its name kept the comma. The new ids start at 12, not 9: the failed load in the previous example had already used up 9 to 11.
  4. For production imports, a common pattern is to LOAD DATA into a staging table with loose types and no constraints, validate and clean it with SQL, then INSERT … SELECT into the real table.
AdvancedLOAD DATA LOCAL is off by default

LOAD DATA LOCAL

LOAD DATA LOCAL INFILE reads the file from the client's machine instead of the server's — what you need when the database is managed (RDS, Cloud SQL) and you can't put files on the server. Because a malicious server could ask a client to send any local file, it's off by default on both sides in MySQL 8+:

SQL
SELECT @@local_infile;
LOAD DATA LOCAL INFILE '/tmp/new_products.csv' INTO TABLE products;
Result
+----------------+
| @@local_infile |
+----------------+
|              0 |
+----------------+
1 row in set (0.000 sec)

ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
What’s happening
  1. The server variable local_infile is 0 (off), MySQL 8's default.
  2. The statement fails before any file is read: ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides. (The manual's security page quotes the message under error 3950; 3948 is what this 9.7 client and server actually return.)
  3. To use it you'd need SET GLOBAL local_infile = 1 on the server and mysql --local-infile=1 (or the driver option — infileStreamFactory in Node's mysql2) on the client. We didn't enable it in this shared sandbox, so a successful LOCAL load isn't shown here.

#Safe updates and the dangers of a missing WHERE

Added

The most expensive bug in SQL is a two-word omission: UPDATE accounts SET balance = 0 — no WHERE, every row changed, and with autocommit it's committed before you've finished reading the "rows affected" number. DELETE without WHERE is the same with less left to look at. The engine did exactly what you asked; there's no undo button.

The defence is layers, like a table saw's blade guard, push stick and emergency stop: habits that make the mistake unlikely (write the SELECT first), settings that refuse obviously dangerous statements (safe-updates mode), transactions that let you look before you commit, and backups for when everything else fails.

SQL
SET SESSION sql_safe_updates = 1;

UPDATE employees SET salary = salary * 1.10;
DELETE FROM orders WHERE status = 'cancelled';

UPDATE employees SET salary = salary * 1.10 WHERE id = 9;
DELETE FROM orders WHERE status = 'cancelled' LIMIT 1;
Result
Query OK, 0 rows affected (0.000 sec)

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`ch04`.`order_items`, CONSTRAINT `order_items_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`))
What’s happening
  1. sql_safe_updates = 1 makes the server reject UPDATE and DELETE statements that don't restrict rows by a key (an indexed column in WHERE) and don't have a LIMIT.
  2. The first statement has no WHERE at all: ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
  3. The second has a WHERE, but status isn't indexed, so MySQL would scan the whole table: rejected with the same error. Safe mode checks for a key, not just for the word WHERE.
  4. WHERE id = 9 uses the primary key, so it's allowed (Rows matched: 1). LIMIT 1 also satisfies the check, even on an unindexed column — order 4 is cancelled but still has an item, so that delete then fails on the foreign key with ERROR 1451.
  5. The mysql client turns this on with --safe-updates (alias --i-am-a-dummy), which also sets sql_select_limit = 1000 and max_join_size = 1000000. MySQL Workbench enables safe updates by default; many people meet error 1175 there first.
AdvancedLook before you commit

The best safety net costs nothing: run the change inside a transaction, check the result, then decide.

SQL
START TRANSACTION;

UPDATE products SET price = price * 0.9 WHERE category_id = 2;
SELECT id, name, price FROM products WHERE category_id = 2;

ROLLBACK;
SELECT id, name, price FROM products WHERE category_id = 2;
Result
Query OK, 0 rows affected (0.000 sec)

Query OK, 2 rows affected (0.000 sec)
Rows matched: 2  Changed: 2  Warnings: 0

+----+--------------+---------+
| id | name         | price   |
+----+--------------+---------+
|  1 | ThinkBook 14 |  809.10 |
|  2 | MacBook Air  | 1169.10 |
+----+--------------+---------+
2 rows in set (0.000 sec)

Query OK, 0 rows affected (0.001 sec)

+----+--------------+---------+
| id | name         | price   |
+----+--------------+---------+
|  1 | ThinkBook 14 |  899.00 |
|  2 | MacBook Air  | 1299.00 |
+----+--------------+---------+
2 rows in set (0.000 sec)
What’s happening
  1. START TRANSACTION switches off autocommit for the next statements; nothing is permanent until COMMIT.
  2. The UPDATE gives laptops 10% off: ThinkBook 14 899.00 → 809.10, MacBook Air 1299.00 → 1169.10. Rows matched: 2 — the number you expected.
  3. The SELECT inside the transaction shows the new prices (your own transaction sees its changes; other sessions still see the old prices).
  4. Here we ROLLBACK, and the prices are back to 899.00 and 1299.00. If the matched count had been 2,000 instead of 2, this is where you'd stop. If it's right, COMMIT.
  5. Keep such transactions short: the updated rows stay locked until you commit or roll back, so a forgotten open transaction in a GUI client blocks other writers.
AdvancedWHERE bugs safe mode won't catch

Two WHERE-clause bugs that safe mode won't catch

A WHERE clause can be present, use a key, and still match far more than you meant:

SQL
SELECT id, customer_id, status FROM orders
WHERE status = 'pending' OR status = 'paid' AND customer_id = 1;

SELECT id, customer_id, status FROM orders
WHERE (status = 'pending' OR status = 'paid') AND customer_id = 1;
Result
+----+-------------+---------+
| id | customer_id | status  |
+----+-------------+---------+
|  2 |           1 | paid    |
|  6 |           4 | pending |
+----+-------------+---------+
2 rows in set (0.000 sec)

+----+-------------+--------+
| id | customer_id | status |
+----+-------------+--------+
|  2 |           1 | paid   |
+----+-------------+--------+
1 row in set (0.000 sec)
What’s happening
  1. The intent: "customer 1's pending or paid orders". But AND binds tighter than OR, so the first query means status = 'pending' OR (status = 'paid' AND customer_id = 1).
  2. It returns order 6 (Sofia's pending order — not customer 1's at all) and order 2 (Rohit's paid order).
  3. With parentheses, the second query returns only order 2. As an UPDATE, the first version would have modified another customer's order.
  4. Rule: whenever AND and OR appear together in a WHERE, add parentheses — even when they're not needed.

The other classic is AND where a comma belongs in SET:

SQL
UPDATE employees
SET salary = salary * 1.1 AND department_id = 2
WHERE id = 9;

SELECT id, name, salary, department_id FROM employees WHERE id = 9;
Result
Query OK, 1 row affected (0.004 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+----+------+--------+---------------+
| id | name | salary | department_id |
+----+------+--------+---------------+
|  9 | Isla |   0.00 |             3 |
+----+------+--------+---------------+
1 row in set (0.000 sec)
What’s happening
  1. The intent was "give Isla a 10% raise and move her to Sales". The author wrote AND instead of a comma between the two assignments.
  2. MySQL parses it as one assignment: salary = ((salary * 1.1) AND (department_id = 2)). Isla's department is 3, so department_id = 2 is 0 (false), and 79200 AND 0 is 0.
  3. Rows matched: 1 Changed: 1 Warnings: 0 — no error, no warning. Isla's salary is now 0.00, and her department is unchanged.
  4. A strict type system would reject assigning a boolean to a DECIMAL; MySQL treats booleans as integers, so it's valid SQL. Reading back the row after every manual UPDATE (inside the transaction) is how you catch this.
Spot the bug

"Mark every order of customers in Toronto as shipped":

SQL
UPDATE orders
SET status = 'shipped'
WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Toronto')
   OR status = 'paid';
Show answer

The OR status = 'paid' applies to all customers, so Toronto's orders and every paid order anywhere get shipped.

SQL
SELECT id, customer_id, status FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE city = 'Toronto')
   OR status = 'paid';
Result
+----+-------------+---------+
| id | customer_id | status  |
+----+-------------+---------+
|  1 |           1 | shipped |
|  2 |           1 | paid    |
|  7 |           5 | paid    |
+----+-------------+---------+
3 rows in set (0.000 sec)
What’s happening
  1. Previewing the WHERE as a SELECT shows which rows the UPDATE would touch.
  2. Toronto's customers are Rohit (1) and Noah (5): their orders are 1, 2 and 7.
  3. OR status = 'paid' adds every paid order regardless of city — in this data both paid orders (2 and 7) already belong to Toronto customers, so the result happens to look right.
  4. That's the dangerous part: on today's data the bug is invisible, and next week a paid order from Mumbai gets marked shipped. If the intent was "Toronto's paid orders", it's AND; if it really was "or", it should be a separate statement with its own review.

SQL and MySQL written fresh; MongoDB built from Rohit’s “mongoDB interview” doc. Every query was run on MySQL 9.7, MongoDB 9.0, Redis 8.10 or PostgreSQL 18.

RohitDownloads · All chapters

Sync progress across devices

Type the same private phrase on your Mac and your phone, and your Learned ticks follow you between them — on every study site.

The phrase never leaves this device: only a fingerprint of it is sent, and the server stores a fingerprint of that. Anyone who knows the phrase could see or change your ticks, so pick something you don’t use elsewhere.

Sync is on in this browser.

Sync ID

This ID must be the same on every device. If another device shows a different one, its phrase is different (capital letters count): tap “Turn off here” on it and type the phrase again exactly.