Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 75 ยท Changing Data

INSERT, UPDATE and DELETE in MySQL

Change data in MySQL safely: multi-row INSERT, INSERT SET, UPDATE and DELETE with ORDER BY and LIMIT, TRUNCATE versus DELETE and the danger of REPLACE.

Reading data is only half the job. Applications add rows, correct them and remove them all day long, and a careless UPDATE or DELETE is one of the quickest ways to damage a database. MySQL follows standard SQL for these three statements and adds practical extras: INSERT ... SET, ORDER BY and LIMIT on UPDATE and DELETE, a fast TRUNCATE, and the notorious REPLACE.

This lesson covers each one, shows what the server reports back (affected rows), and points out where MySQL differs from the standard and from SQLite, which runs the live examples. If you need a gentler introduction first, read the SQL INSERT, UPDATE and DELETE lessons.

Three statements, one habit

INSERT Add one or many rows.
UPDATE Change values in matching rows.
DELETE Remove matching rows.

Habit: write the WHERE first, run it as a SELECT, then switch the verb.

1 / 2

INSERT

The basic form lists the columns and then one or more rows of values. Inserting several rows in one statement is much faster than one statement per row, because the server parses and commits once:

SQL
INSERT INTO products (sku, name, price, stock)
VALUES ('PEN-01', 'Pen', 1.50, 100),
       ('BOOK-7', 'Notebook', 4.25, 40),
       ('LAMP-3', 'Desk lamp', 19.00, 8);

Other forms MySQL accepts:

SQL
-- INSERT ... SET: one row, column = value pairs (MySQL-only syntax, easy to read)
INSERT INTO products SET sku = 'MUG-2', name = 'Mug', price = 7.00;

-- INSERT ... SELECT: copy rows from a query
INSERT INTO products_archive (sku, name, price)
SELECT sku, name, price FROM products WHERE discontinued = 1;

-- use the DEFAULT keyword to say "the column default"
INSERT INTO products (sku, name, price, stock) VALUES ('CAP-9', 'Cap', 9.00, DEFAULT);

Things to remember:

  • Always name the columns. INSERT INTO products VALUES (...) breaks as soon as someone adds a column to the table.
  • Columns you leave out get their default, NULL if they have none, or the next number if they are AUTO_INCREMENT. In strict mode (the default) a NOT NULL column without a default makes the statement fail with an error instead of getting a silent zero (SQL Modes and Strictness).
  • LAST_INSERT_ID() returns the AUTO_INCREMENT value generated by your own session's last insert. For a multi-row insert it returns the first generated id of that statement, not the last (AUTO_INCREMENT, UUIDs and ID Strategies).
  • A very large multi-row insert is limited by max_allowed_packet. Batches of a few hundred to a few thousand rows are the sweet spot (Bulk Loading and Exporting Data).
SQL Multi-row insert and INSERT ... SELECT

Press Run (or Ctrl + Enter) to see the result here.

Four products are stored with ids 1 to 4, and the mug shows stock 0 because the column default applied. The archive holds the pen and the notebook, copied by one INSERT ... SELECT. (SQLite spells the id functions last_insert_rowid() and changes(). MySQL's equivalents are LAST_INSERT_ID() and ROW_COUNT().)

UPDATE

UPDATE changes columns in every row that matches the WHERE clause. Without a WHERE, it changes every row in the table. MySQL's command-line client has a seat belt for that mistake: starting it with --safe-updates (or SET sql_safe_updates = 1) makes it refuse an UPDATE or DELETE that has no key-based WHERE or LIMIT.

SQL
UPDATE products SET price = price * 1.10 WHERE sku LIKE 'BOOK%';

-- MySQL extra: ORDER BY and LIMIT on a single-table UPDATE
UPDATE jobs
SET status = 'running', worker = 'w1'
WHERE status = 'queued'
ORDER BY priority DESC, id
LIMIT 5;

The ORDER BY ... LIMIT form is a common way to "claim" the next few jobs from a queue. Rules and surprises:

  • ORDER BY and LIMIT work only with single-table updates. A multi-table update (UPDATE a JOIN b ON ... SET a.x = b.y) cannot use them (Joins in MySQL).
  • Affected rows means changed rows. If a matched row already has the new value, MySQL does not count it. The client reports "Rows matched: 3 Changed: 1". Libraries can ask for matched rows instead through a connection flag, so do not assume the number means the same everywhere.
  • Assignments run left to right and see earlier changes. In UPDATE t SET a = a + 1, b = a, the second assignment reads the new a. The SQL standard (and SQLite) would give b the old value. When you swap columns, rely on this carefully or use a temporary value.
  • Updating a column used by an index, or many rows at once, takes row locks for the whole transaction, so large updates are best done in batches (Locking, Gap Locks and Deadlocks).

SQLite normally lacks UPDATE ... ORDER BY ... LIMIT, but you can express the same thing with a subquery that picks the ids:

SQL Claiming the next jobs from a queue

Press Run (or Ctrl + Enter) to see the result here.

The highest priorities are the backup (9) and "Resize images" (5, lower id than "Build report"), so those two rows become running for worker w1. changed_rows is 2. In MySQL you would write the single statement shown in the comment. (In MySQL you cannot use LIMIT inside an IN subquery, which is one more reason the built-in UPDATE ... LIMIT is handy.)

DELETE

SQL
DELETE FROM sessions WHERE expires_at < NOW();

-- MySQL extra: delete in small batches
DELETE FROM sessions WHERE expires_at < NOW() ORDER BY id LIMIT 5000;

Deleting millions of rows in one statement holds locks, bloats the undo log and can stall replication. The usual cure is the batch loop above: run the statement repeatedly from a script until it reports 0 affected rows, pausing briefly between rounds (Transactions and ACID in InnoDB). Like UPDATE, DELETE ... ORDER BY ... LIMIT is limited to a single table. A multi-table form exists (DELETE o FROM orders o JOIN customers c ON ... WHERE c.closed = 1) and removes rows only from the tables named before FROM.

Deleting a parent row interacts with foreign keys: depending on the constraint, MySQL rejects the delete, cascades to child rows, or sets the child column to NULL (Foreign Keys in MySQL).

DELETE versus TRUNCATE

To empty a whole table you have two choices:

DELETE FROM t TRUNCATE TABLE t
Kind of statement DML DDL (drops and recreates the table internally)
Speed on a big table slow, row by row very fast
Rollback yes, inside a transaction no, it commits implicitly
AUTO_INCREMENT counter keeps counting resets to 1
Delete triggers fire for every row do not fire
Referenced by a foreign key allowed (checks apply) refused (error 1701)
Affected-rows count the number deleted reports 0

Use TRUNCATE for staging and test tables, and DELETE whenever you need a transaction, triggers or a WHERE.

SQL DELETE keeps the counter, TRUNCATE resets it

Press Run (or Ctrl + Enter) to see the result here.

After a plain DELETE the next id continues at 4. Removing the counter makes numbering restart at 1, which is what TRUNCATE TABLE does automatically in MySQL (SQLite has no TRUNCATE statement at all).

REPLACE: powerful and dangerous

REPLACE works like INSERT, but when the new row collides with an existing row on a PRIMARY KEY or UNIQUE index, MySQL deletes the old row and inserts the new one:

SQL
REPLACE INTO products (sku, name, price) VALUES ('PEN-01', 'Pen', 1.80);

That sounds like an update, but it is not, and the difference bites:

  • Columns you do not mention get their defaults, not their old values, so the stock of that pen silently becomes 0.
  • The delete fires ON DELETE triggers, and with ON DELETE CASCADE foreign keys it deletes the child rows (order lines, comments) as well.
  • The row may get a new AUTO_INCREMENT id if the conflict was on a different unique key, and if other tables point at the old id they are now orphaned or cascaded away.
  • The affected-rows count is 2 when a row was replaced (one delete plus one insert).

SQLite's INSERT OR REPLACE behaves the same way, which makes the damage easy to watch:

SQL REPLACE deletes the old row first

Press Run (or Ctrl + Enter) to see the result here.

Before the replace there are 2 order lines and 100 pens in stock. Afterwards the pen's stock is 0 and both order lines are gone, because replacing deleted the parent row and the cascade followed. The plain UPDATE at the end touches only the price. The better tools for "insert or change" are INSERT ... ON DUPLICATE KEY UPDATE and INSERT IGNORE, covered in the next lesson.

Finding and removing duplicate rows

Duplicates appear when a table has no UNIQUE constraint on the column that is supposed to identify a row: an email address imported twice, a form submitted twice, a job that ran again. Cleaning them up is a three-step routine: find them, choose which copy to keep, delete the others, and then prevent them with a constraint.

Find them

Group by the columns that define "the same" and keep groups with more than one row:

SQL
SELECT email, COUNT(*) AS copies
FROM subscribers
GROUP BY email
HAVING COUNT(*) > 1;

To see the individual extra rows, number the copies inside each group with ROW_NUMBER() (MySQL 8.0 and later). Every row with a number above 1 is a surplus copy:

SQL
SELECT id, email,
       ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS copy_number
FROM subscribers;

The ORDER BY id inside the window decides which copy is number 1, so it is also your rule for which row survives. Here the oldest row (lowest id) is kept. Use a timestamp column instead if you prefer the newest.

Delete the extra copies

On a MySQL 8.4 server with six rows (three for Ada, two for Ben, one for Cy), two safe ways work. The first is a self join, a MySQL multi-table DELETE that removes every row for which an older row with the same email exists:

SQL
DELETE s1
FROM subscribers AS s1
JOIN subscribers AS s2
  ON s1.email = s2.email AND s1.id > s2.id;
-- ids 3, 5 and 6 are deleted; ids 1, 2 and 4 remain

The second uses the window function. MySQL does not allow a DELETE to read the table it is deleting from in a plain subquery (error 1093, "You can't specify target table for update in FROM clause"), but wrapping the numbering in a derived table is accepted because it is materialized first:

SQL
DELETE FROM subscribers
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS copy_number
    FROM subscribers
  ) AS numbered
  WHERE copy_number > 1
);

Both left ids 1, 2 and 4 in the test. The self join is shorter. The window version is easier to adapt to rules such as "keep the newest" or "keep the one with a non-empty name". On a very large table, delete in batches (Habits that prevent disasters) and take a backup or run the statement in a transaction first.

Stop them coming back

Once the table is clean, add a unique index so the database rejects new duplicates, whatever the application does:

SQL
ALTER TABLE subscribers ADD UNIQUE KEY uq_subscribers_email (email);

INSERT INTO subscribers (email, name) VALUES ('[email protected]', 'Dup');
-- ERROR 1062 (23000): Duplicate entry '[email protected]' for key 'subscribers.uq_subscribers_email'

Adding the index fails while duplicates still exist, which is why you clean first. After that, use INSERT IGNORE or ON DUPLICATE KEY UPDATE when a repeat is expected (UPSERT and INSERT IGNORE), and see Keys and Constraints for choosing what should be unique.

The live example below runs the window-function approach in SQLite. SQLite has no multi-table DELETE ... JOIN, but it accepts the same derived-table form, so the statement is valid in MySQL 8 as written. At the end, INSERT OR IGNORE plays the role of MySQL's INSERT IGNORE.

SQL Find duplicates, keep the oldest, add a unique index

Press Run (or Ctrl + Enter) to see the result here.

Step 1 lists [email protected] with 3 copies and [email protected] with 2. Step 2 numbers them, and step 3 removes ids 3, 5 and 6, leaving Ada, Ben and Cy. After the unique index, the extra insert is silently skipped and the table still has 3 rows.

Habits that prevent disasters

  • Write the WHERE clause first and run it as a SELECT to see which rows it matches, then change the verb.
  • Wrap risky changes in a transaction and check the affected-rows count before you COMMIT.
  • Keep sql_safe_updates on in interactive sessions.
  • Prefer batches with LIMIT for big deletes and updates.
  • Do not use REPLACE to "update". Use UPDATE or an upsert.

What ran live here

Multi-row INSERT, INSERT ... SELECT, defaults, a subquery version of UPDATE ... ORDER BY ... LIMIT, DELETE versus the counter, INSERT OR REPLACE with a foreign-key cascade, and the duplicate-row cleanup with ROW_NUMBER() ran in SQLite. The self-join DELETE and the duplicate-key error message in the duplicates section were run on a MySQL 8.4 server but are shown as read-only code. The MySQL-only parts, INSERT ... SET, UPDATE/DELETE ... LIMIT, TRUNCATE, sql_safe_updates and the left-to-right assignment rule, are shown as read-only code and tables.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why is one INSERT with many VALUES rows usually faster than many single-row INSERTs?

    Per-statement overhead (network round trip, parsing, commit) is paid once. Constraints and indexes still apply to every row.
  2. 2. Which statement cannot be rolled back in MySQL because it commits implicitly?

    TRUNCATE is DDL. It implicitly commits and cannot be undone with ROLLBACK, unlike DELETE.
  3. 3. What does REPLACE INTO do when the new row collides with an existing primary key?

    REPLACE is a delete followed by an insert. Unlisted columns fall back to defaults and delete triggers and cascades fire.
  4. 4. In MySQL, UPDATE t SET a = a + 1, b = a runs left to right. What does b receive?

    MySQL evaluates assignments from left to right, so b sees the already incremented a. The SQL standard would give b the old value.
  5. 5. What does LAST_INSERT_ID() return after a single INSERT that adds three rows?

    For a multi-row insert, LAST_INSERT_ID() reports the first AUTO_INCREMENT value generated by the statement. The others follow consecutively.

Summary

  • Name your columns, insert in multi-row batches, and use INSERT ... SELECT to copy data. INSERT ... SET is a readable MySQL-only form.
  • UPDATE and DELETE need a careful WHERE. In MySQL they also accept ORDER BY and LIMIT on a single table, which is ideal for claiming queue rows and for batch deletes.
  • Affected rows for UPDATE means rows actually changed. Assignments are evaluated left to right.
  • TRUNCATE is fast DDL: no rollback, no triggers, resets AUTO_INCREMENT, refused on referenced tables. DELETE is slower but transactional.
  • REPLACE deletes and re-inserts, which resets unlisted columns and can cascade. Prefer UPDATE or an upsert.

Next: Upserts: ON DUPLICATE KEY UPDATE and INSERT IGNORE.