Lesson 06 beginner

Inserting, Updating and Deleting Data

Fill Orbit with data and change it safely: single and multi-row INSERT, LAST_INSERT_ID, INSERT ... SELECT, upserts with ON DUPLICATE KEY UPDATE and row aliases, INSERT IGNORE and why REPLACE is dangerous, UPDATE and DELETE with ORDER BY and LIMIT, multi-table UPDATE and DELETE with joins, affected-row counts, safe update mode and TRUNCATE.

Your tables are empty. In this lesson you load Orbit’s first users, notes and tags, then learn every way MySQL lets you change data, including a few that are easy to get wrong.

Insert rows

Insert several rows in one statement; it is much faster than one statement per row:

INSERT INTO users (email, name, created_at) VALUES
  ('ana@orbit.dev',  'Ana Silva',  '2026-09-28 10:00:00'),
  ('ben@orbit.dev',  'Ben Carter', '2026-09-29 11:30:00'),
  ('chen@orbit.dev', 'Chen Wei',   '2026-09-30 16:45:00');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

id and plan were left out, so AUTO_INCREMENT and the default 'free' filled them in. Normally you would leave created_at out too and let CURRENT_TIMESTAMP fill it; fixed dates here keep the results in this course reproducible.

Tip: Always list the columns. INSERT INTO users VALUES (...) depends on the column order and breaks the day someone adds a column.

Which id did MySQL assign? LAST_INSERT_ID() tells you, per connection, so other clients inserting at the same time do not affect it:

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

After a multi-row insert it returns the id of the first row in the batch, not the last. Drivers expose the same value, for example cursor.lastrowid in Python or insertId in Node.js.

Now the notes, with fixed dates for the same reason:

INSERT INTO notes (user_id, title, body, pinned, created_at) VALUES
  (1, 'Shopping list',    'eggs, milk, coffee',                   TRUE,  '2026-10-01 09:00:00'),
  (1, 'Deploy checklist', 'run migrations, then restart the api', FALSE, '2026-10-03 14:30:00'),
  (2, 'Book ideas',       'a database detective story',           FALSE, '2026-10-05 20:15:00'),
  (2, 'MySQL tips',       'always use utf8mb4 for text',          TRUE,  '2026-10-12 08:45:00'),
  (1, 'Meeting notes',    'discuss the backup policy',            FALSE, '2026-11-02 10:00:00');

INSERT INTO tags (name) VALUES ('work'), ('personal'), ('databases');

INSERT INTO note_tags (note_id, tag_id) VALUES
  (1, 2), (2, 1), (3, 2), (4, 1), (4, 3), (5, 1);

Ana now has three notes, Ben two and Chen none, which will matter when you join tables in lesson 8.

To copy rows produced by a query instead of literal values, use INSERT ... SELECT:

CREATE TABLE notes_archive LIKE notes;
INSERT INTO notes_archive SELECT * FROM notes WHERE created_at < '2026-01-01';
DROP TABLE notes_archive;

Update rows

UPDATE changes the rows that match WHERE. Ben upgrades to the pro plan:

UPDATE users SET plan = 'pro' WHERE email = 'ben@orbit.dev';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Run the same statement again:

Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

MySQL reports changed rows as “affected”: the row matched, but it already had that value, so nothing was written. Keep that in mind when your code checks the affected-row count to decide whether a row exists.

You can set several columns and use the current value in the expression:

UPDATE notes
SET title = CONCAT(title, ' (old)'), pinned = FALSE
WHERE id = 2;
UPDATE notes SET title = 'Deploy checklist' WHERE id = 2;

UPDATE also accepts ORDER BY and LIMIT, which is handy for working through large tables in small batches:

UPDATE notes SET pinned = FALSE WHERE pinned ORDER BY created_at LIMIT 100;

That would unpin up to 100 of the oldest pinned notes. Don’t run it now; Orbit’s pinned notes stay as they are.

Upserts: insert or update

A sign-up form or a sync job often has to “insert this user, or update them if they already exist”. The unique key on email makes that one atomic statement. Chen buys the pro plan through a checkout that always sends the full record:

INSERT INTO users (email, name, plan) VALUES ('chen@orbit.dev', 'Chen Wei', 'pro') AS new
ON DUPLICATE KEY UPDATE plan = new.plan;
Query OK, 2 rows affected (0.01 sec)

The affected-row count tells you what happened:

CountMeaning
1A new row was inserted
2An existing row was updated
0The row existed and already had these values

AS new names the row you tried to insert, so new.plan is “the value from the statement”. This row-alias syntax arrived in MySQL 8.0.19 and replaces the older VALUES(plan) function, which is deprecated.

INSERT IGNORE

INSERT IGNORE skips rows that would violate a unique key instead of failing the whole statement:

INSERT IGNORE INTO users (email, name) VALUES ('ana@orbit.dev', 'Ana Silva');
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1062 | Duplicate entry 'ana@orbit.dev' for key 'users.uq_users_email' |
+---------+------+----------------------------------------------------------------+
1 row in set (0.00 sec)

Use it carefully: IGNORE also downgrades other errors, such as invalid values, to warnings, so bad data can slip in. ON DUPLICATE KEY UPDATE id = id is a stricter way to say “skip duplicates”.

Why REPLACE is dangerous

REPLACE INTO looks like an upsert, but when a row exists it deletes it and inserts a new one. With Orbit’s foreign keys, REPLACE INTO users ... for Ana would delete her row, which cascades and deletes all her notes, then insert a fresh Ana with a new id. Prefer INSERT ... ON DUPLICATE KEY UPDATE almost always.

Delete rows

INSERT INTO notes (user_id, title) VALUES (3, 'Temporary');
SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)
DELETE FROM notes WHERE id = 6;
Query OK, 1 row affected (0.00 sec)

Like UPDATE, DELETE accepts ORDER BY and LIMIT, so you can purge old data in batches without one huge transaction:

DELETE FROM notes WHERE created_at < '2020-01-01' ORDER BY id LIMIT 1000;

Repeat until it reports 0 rows. Small batches keep locks short and replication lag low. The next note inserted gets id 7, not 6: AUTO_INCREMENT never reuses ids.

Updating and deleting across tables

MySQL lets one statement change rows based on another table, using a join:

UPDATE notes n
JOIN users u ON u.id = n.user_id
SET n.updated_at = NOW()
WHERE u.plan = 'pro';
DELETE n
FROM notes n
JOIN users u ON u.id = n.user_id
WHERE u.email = 'spam@example.com';

In the multi-table DELETE, the names right after DELETE say which table loses rows; the others only filter. DELETE n, u FROM ... would delete from both. Multi-table statements cannot use ORDER BY or LIMIT.

Safe update mode

A missing or wrong WHERE clause is the classic disaster:

UPDATE notes SET pinned = FALSE;   -- every note, not just one!

The client option --safe-updates (or SET sql_safe_updates = 1;) makes MySQL refuse UPDATE and DELETE statements whose WHERE does not use a key column and that have no LIMIT:

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

Turn it on in ~/.my.cnf under [mysql] as safe-updates for interactive sessions on important servers. Also make it a habit to run the WHERE clause as a SELECT COUNT(*) first, and to change production data inside a transaction (lesson 14) so you can roll back.

Emptying a table

DELETE FROM t; removes rows one by one, logs each one and keeps AUTO_INCREMENT where it was. TRUNCATE TABLE t; drops and recreates the table: instant even for millions of rows, resets AUTO_INCREMENT, but cannot be rolled back and fails if another table has a foreign key pointing at it.

Check the result

After this lesson Orbit’s users look like this:

SELECT id, email, name, plan FROM users ORDER BY id;
+----+----------------+------------+------+
| id | email          | name       | plan |
+----+----------------+------------+------+
|  1 | ana@orbit.dev  | Ana Silva  | free |
|  2 | ben@orbit.dev  | Ben Carter | pro  |
|  3 | chen@orbit.dev | Chen Wei   | pro  |
+----+----------------+------------+------+
3 rows in set (0.00 sec)

Quick reference

TaskSQL
Insert several rowsINSERT INTO t (a, b) VALUES (1, 2), (3, 4);
Id of the inserted rowSELECT LAST_INSERT_ID();
Copy rowsINSERT INTO t2 (a) SELECT a FROM t1 WHERE ...;
UpsertINSERT ... AS new ON DUPLICATE KEY UPDATE col = new.col;
Skip duplicatesINSERT IGNORE ... (watch the warnings)
UpdateUPDATE t SET a = 1 WHERE id = 5;
Batch deleteDELETE FROM t WHERE ... ORDER BY id LIMIT 1000;
Join updateUPDATE a JOIN b ON ... SET a.x = b.y WHERE ...;
Empty a tableTRUNCATE TABLE t;

Exercise

An ON DUPLICATE KEY UPDATE statement reports “2 rows affected”. What happened?

  1. Two new rows were inserted
  2. One row was inserted and one deleted
  3. An existing row was updated
  4. The statement failed and was retried
Show answer

Answer: C. MySQL reports 1 for an insert, 2 for an update of an existing row and 0 when the existing row already had the same values.

Exercise

Why can REPLACE INTO users be destructive in the Orbit schema?

  1. It ignores the unique key on email
  2. It deletes the existing row first, which cascades to the user’s notes
  3. It locks the whole database
  4. It resets AUTO_INCREMENT to 1
Show answer

Answer: B. REPLACE is a DELETE followed by an INSERT. Deleting a user fires ON DELETE CASCADE on notes, and the new row gets a new id.

Exercise

You insert three users in one INSERT and the rows get ids 10, 11 and 12. What does LAST_INSERT_ID() return?

  1. 10
  2. 12
  3. 3
  4. NULL, because several rows were inserted
Show answer

Answer: A. For a multi-row insert, LAST_INSERT_ID() returns the first AUTO_INCREMENT value generated by the statement.

Key takeaways

  • List columns in every INSERT and insert many rows per statement.
  • LAST_INSERT_ID() is per connection and returns the first id of a multi-row insert.
  • Upsert with INSERT ... AS new ON DUPLICATE KEY UPDATE; avoid REPLACE on tables with children.
  • MySQL’s affected-row count only counts rows that actually changed.
  • Batch big updates and deletes with ORDER BY ... LIMIT, and use safe update mode interactively.

FAQ

Does MySQL support RETURNING like PostgreSQL?

No. MySQL has no RETURNING clause for INSERT, UPDATE or DELETE. Use LAST_INSERT_ID() for generated ids, or select the rows in the same transaction before or after the change.

How do I insert a row only if it does not exist?

Put a unique key on the column(s) that define “exists” and use INSERT ... ON DUPLICATE KEY UPDATE id = id or INSERT IGNORE. Checking with a SELECT first is not safe: another connection can insert between your check and your insert.

Why did my upsert make AUTO_INCREMENT jump?

InnoDB reserves an id before it knows whether the insert will hit a duplicate. When the statement turns into an update, that id is simply not used. Gaps are normal and harmless.

How do I reset AUTO_INCREMENT after testing?

TRUNCATE TABLE t; resets it to 1. Otherwise ALTER TABLE t AUTO_INCREMENT = 100; sets the next value, but it can never go below the current maximum id plus one.

↑ ↓ navigate Enter open Esc close