SQL ·
‹ Previous Next ›
⏱ 5 min read Modified: 2026-11-17

SQL INSERT, UPDATE and DELETE: DML Commands in MySQL

You need to rename one forum thread, but the condition is missing: UPDATE topics SET title = 'City bikes';. MySQL replies Rows matched: 2 Changed: 2 — every thread in the database now has that title, and in the default autocommit mode there is nothing to undo it with. The mysql client started with --safe-updates would have rejected the command with error 1175.

DML (Data Manipulation Language) is the group of SQL commands that add, change and delete table rows: INSERT, UPDATE and DELETE. They do not touch the table structure — that is the job of DDL commands such as CREATE TABLE, ALTER TABLE and DROP. Unlike DDL, DML commands in InnoDB are transactional: if a transaction was started before them, their changes can be undone with ROLLBACK.

DML Commands: INSERT, UPDATE, DELETE

DML has three commands that change data: INSERT adds new rows, UPDATE changes values in existing rows, and DELETE removes rows. UPDATE and DELETE affect the rows that match the WHERE condition — and without a condition, every row of the table.

Command What it does Example What the mysql client reports
INSERT Adds one or more rows INSERT INTO tags (name) VALUES ('java'); Query OK, 1 row affected
UPDATE Changes column values in the matched rows UPDATE topics SET views = views + 1 WHERE id = 1; Rows matched: 1 Changed: 1
DELETE Removes the matched rows; the table structure stays DELETE FROM topic_tags WHERE topic_id = 3; Query OK, 1 row affected

By the SQL standard, SELECT also belongs to DML, but it is usually put in a separate group, DQL: it reads data without changing it and is covered in the module lesson on the SELECT statement.

The examples run in order on the forum practice database. Recreate it with the script from The Forum Practice Database (three users, two threads, four messages, three tags) in the mysql client (see Your First SQL Query in MySQL), then add two columns to the threads table that this lesson needs. The syntax is MySQL 8 with the InnoDB engine.

ALTER TABLE topics
    ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN views      INT      NOT NULL DEFAULT 0;

How ALTER TABLE works is explained in ALTER TABLE: Changing a Table. The existing threads got the current time in created_at and 0 in views.

SQL INSERT INTO: Adding Rows

The command INSERT INTO table (columns) VALUES (values); adds a new row to a table: the values go into the listed columns in order, and the columns missing from the list get their default value. The number of values in the VALUES parentheses must equal the number of columns.

INSERT INTO users (username, email) VALUES ('Dave', '[email protected]');
SELECT * FROM users WHERE username = 'Dave';
Query OK, 1 row affected (0.01 sec)
+----+----------+------------------+-------+
| id | username | email            | phone |
+----+----------+------------------+-------+
|  4 | Dave     | [email protected] | NULL  |
+----+----------+------------------+-------+

The database assigned the id: the column has AUTO_INCREMENT, and Dave became the fourth user. phone was not given and allows NULL, so it got NULL. String values and dates go in single quotes, numbers without quotes (date formats are covered in Date and Time: DATE, DATETIME, TIMESTAMP).

INSERT Without a Column List

You can omit the column list, but then you must provide a value for every column of the table, strictly in the order they were declared, including id:

INSERT INTO users VALUES (NULL, 'Eve', '[email protected]', NULL);
Query OK, 1 row affected (0.01 sec)

This form is shorter but fragile. The command below worked on the topics table from the keys lesson, which had three columns — after the ALTER TABLE above it has five:

INSERT INTO topics VALUES (NULL, 'No column list', 1);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

In application code, always list the columns explicitly: then adding a new column with a default value will not break a single existing INSERT.

Inserting Multiple Rows with One INSERT

To insert several rows, list the value sets separated by commas after a single VALUES:

INSERT INTO tags (name) VALUES ('java'), ('sql'), ('mysql');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

One command for three rows runs noticeably faster than three separate ones: one round trip to the server and one log write. The command is atomic: if one row violates a constraint, InnoDB inserts none of them.

INSERT INTO tags (name) VALUES ('jdbc'), ('java');
ERROR 1062 (23000): Duplicate entry 'java' for key 'tags.name'

The tag jdbc was not added either, even though it is valid on its own. The size of one command is limited by the max_allowed_packet variable (64 MB by default in MySQL 8), so very large data sets are inserted in batches of a few hundred or a few thousand rows.

DEFAULT, NULL and AUTO_INCREMENT

A column omitted from INSERT gets its DEFAULT value. For an AUTO_INCREMENT column the database issues the next number when the column is omitted or receives NULL (or 0). The keyword DEFAULT in the value list explicitly asks for the default value.

INSERT INTO topics (title, author_id) VALUES ('Ice fishing', 1);
-- the same insert with explicit values:
-- INSERT INTO topics (id, title, author_id, created_at, views)
--     VALUES (NULL, 'Ice fishing', 1, DEFAULT, DEFAULT);
SELECT id, title, created_at, views FROM topics WHERE id = 3;
+----+-------------+---------------------+-------+
| id | title       | created_at          | views |
+----+-------------+---------------------+-------+
|  3 | Ice fishing | 2026-11-17 10:15:42 |     0 |
+----+-------------+---------------------+-------+

created_at holds the moment of the insert. A NOT NULL column without a default, however, cannot be skipped. A typical example from older tutorials fails like this:

INSERT INTO users (username) VALUES ('Frank');
ERROR 1364 (HY000): Field 'email' doesn't have a default value

The error comes from strict mode (STRICT_TRANS_TABLES), which is on by default in MySQL 8. Without it MySQL would insert an empty string and raise only a warning. An explicit NULL in a NOT NULL column gives error 1048 — column constraints are listed in Constraints: NOT NULL, DEFAULT, CHECK, UNIQUE and Keys.

LAST_INSERT_ID(): the id of the Row You Just Inserted

The LAST_INSERT_ID() function returns the AUTO_INCREMENT value generated by the last successful insert in the current connection. Inserts made by other connections do not affect it, so it is safe under concurrent load. After a multi-row INSERT it returns the id of the first inserted row.

SELECT LAST_INSERT_ID();   -- right after inserting the 'Ice fishing' thread
INSERT INTO messages (topic_id, author_id, body)
    VALUES (LAST_INSERT_ID(), 1, 'Is the ice thick enough yet?');
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
Query OK, 1 row affected (0.01 sec)

What happens if you add a second message to the same thread the same way?

INSERT INTO messages (topic_id, author_id, body)
    VALUES (LAST_INSERT_ID(), 3, 'Not yet, it is still thin.');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`forum`.`messages`, CONSTRAINT `fk_messages_topic` FOREIGN KEY (`topic_id`) REFERENCES `topics` (`id`) ON DELETE CASCADE)

The first message insert generated id 5 itself, so LAST_INSERT_ID() now returns the message id, not the thread id. There is no thread with id = 5, and the foreign key rejected the row. Save the id you need in a variable right after inserting the thread:

-- in a script, the correct order is:
-- INSERT INTO topics (title, author_id) VALUES ('Ice fishing', 1);
-- SET @topic_id = LAST_INSERT_ID();
SET @topic_id = 3;   -- the thread is already inserted above, its id is 3
INSERT INTO messages (topic_id, author_id, body) VALUES (@topic_id, 3, 'Not yet, it is still thin.');

In Java the same number is returned by getGeneratedKeys() of a PreparedStatement created with the Statement.RETURN_GENERATED_KEYS flag.

INSERT ... SELECT: Inserting Query Results

Instead of VALUES, you can write a SELECT after the column list: every row of its result is inserted into the table. Columns are matched by position, not by name, so the number and order of the SELECT columns must match the INSERT column list.

Let’s tag all of Alice’s threads (author_id = 1) with “sport” (id = 2) — these are threads 1 and 3:

INSERT INTO topic_tags (topic_id, tag_id)
SELECT id, 2 FROM topics WHERE author_id = 1;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

The constant 2 in the SELECT is put into every row as tag_id. If you run the same command a second time, the pairs already exist, and the composite primary key of topic_tags rejects the insert:

ERROR 1062 (23000): Duplicate entry '1-2' for key 'topic_tags.PRIMARY'

The table in the SELECT may be the same table you insert into: MySQL first stores the query result in a temporary table. The same technique is used to back up a table before a risky change — see the example in Copying a Table: LIKE and AS SELECT.

Duplicate Keys: INSERT IGNORE, ON DUPLICATE KEY UPDATE, REPLACE

If a new row repeats a primary or unique key value, a plain INSERT fails with error 1062. MySQL offers three ways to handle the duplicate without an error: skip the row (INSERT IGNORE), update the existing row (INSERT ... ON DUPLICATE KEY UPDATE, known as an upsert), or delete the old row and insert the new one (REPLACE).

Command What happens on a duplicate key Rows affected per row Gotchas
INSERT Error 1062, the whole command is cancelled — —
INSERT IGNORE The row is skipped; a warning instead of an error 0 Also turns other errors into warnings: NULL in a NOT NULL column, a foreign key violation, a string that is too long
INSERT ... ON DUPLICATE KEY UPDATE The existing row is updated with the expressions after UPDATE 1 — inserted, 2 — updated, 0 — values unchanged With several unique keys only one matching row is updated; an AUTO_INCREMENT number is used up even on update
REPLACE The old row is deleted, the new one is inserted 2 (delete + insert) or more ON DELETE CASCADE / SET NULL and delete triggers fire; columns not listed get their DEFAULT

INSERT IGNORE

Repeat the tag insert from the INSERT ... SELECT section with IGNORE:

INSERT IGNORE INTO topic_tags (topic_id, tag_id)
SELECT id, 2 FROM topics WHERE author_id = 1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
Records: 2  Duplicates: 2  Warnings: 2

No error, both pairs were skipped. That is handy for a junction table, but IGNORE suppresses more than duplicates: INSERT IGNORE INTO topics (title, author_id) VALUES (NULL, 1) does not fail with error 1048 but inserts a thread with an empty title, and a thread with a nonexistent author is silently skipped. Data errors become warnings that nobody reads.

INSERT ... ON DUPLICATE KEY UPDATE (Upsert in MySQL)

An upsert means “insert, and if that key already exists, update”. The username Bob is taken (the username column is UNIQUE), so the command updates the email of the existing user:

INSERT INTO users (username, email)
VALUES ('Bob', '[email protected]') AS new
ON DUPLICATE KEY UPDATE email = new.email;
Query OK, 2 rows affected (0.01 sec)

2 rows affected means “the row was updated”; a new row would give 1, and identical values would give 0. AS new is an alias for the row being inserted, introduced in MySQL 8.0.19. Many examples online use the VALUES(email) function instead: it still works, but it has been deprecated since MySQL 8.0.20 and raises warning 1287 'VALUES function' is deprecated. MariaDB, on the contrary, understands only VALUES().

REPLACE and Why It Is More Dangerous Than It Looks

REPLACE is written like INSERT, but on a duplicate key it does not update the row — it deletes it and inserts a new one. For a table referenced by foreign keys, this is an ordinary DELETE with all its consequences. Carol (id = 3) wants to change her email:

REPLACE INTO users (id, username, email) VALUES (3, 'Carol', '[email protected]');
SELECT id, author_id, body FROM messages WHERE id IN (2, 4);
Query OK, 2 rows affected (0.01 sec)
+----+-----------+-----------------------------------------+
| id | author_id | body                                    |
+----+-----------+-----------------------------------------+
|  2 |      NULL | At the reservoir, early in the morning. |
|  4 |      NULL | Get a hybrid.                           |
+----+-----------+-----------------------------------------+

The email changed, but Carol’s messages lost their author: deleting the users row triggered ON DELETE SET NULL of the fk_messages_author foreign key. If Carol had a profile, ON DELETE CASCADE would have deleted it too. And for Alice, who has threads, the same command fails with error 1451. How these actions work is explained in ON DELETE and ON UPDATE. To update an existing row, use ON DUPLICATE KEY UPDATE or UPDATE.

SQL UPDATE: Changing Rows

The command UPDATE table SET column = value [, ...] WHERE condition; changes column values in every row that matches the WHERE condition. SET can list several columns separated by commas, and a value can be an expression, including one based on the column’s current value. Without WHERE, every row of the table is changed.

UPDATE users
SET phone = '+14155552671',
    email = '[email protected]'
WHERE id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Rows matched is how many rows the condition found, Changed is how many of them actually changed. Run the same command again: the values already match, and MySQL overwrites nothing:

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

Counters are incremented with an expression based on the old value — this way the command works correctly even when two sessions run it at the same time:

UPDATE topics SET views = views + 1 WHERE id = 1;

If UPDATE changes a key value that other tables refer to (for example, UPDATE users SET id = 10 WHERE id = 2), a foreign key without ON UPDATE CASCADE rejects the command with error 1451.

What Will SELECT Print After This UPDATE?

In one SET, increase the view counter of thread 2 and append its value to the title. Right now views of thread 2 is 0:

UPDATE topics
SET views = views + 5,
    title = CONCAT(title, ' [', views, ']')
WHERE id = 2;
SELECT id, title, views FROM topics WHERE id = 2;
+----+-------------+-------+
| id | title       | views |
+----+-------------+-------+
|  2 | Cycling [5] |     5 |
+----+-------------+-------+

The title shows 5, not 0: MySQL evaluates the assignments of a single-table UPDATE from left to right, so title already sees the new views. By the SQL standard, every expression in SET uses the row values from before the change — PostgreSQL, Oracle and SQL Server would return Cycling [0]. When you port queries between databases, such commands produce different data without a single error.

UPDATE Without WHERE and Safe Update Mode (Error 1175)

UPDATE without WHERE changes every row of the table — exactly what happened in the example at the top of this lesson. Safe update mode protects you from this mistake: the mysql client enables it with the --safe-updates option (long synonym --i-am-a-dummy), a session enables it with SET SQL_SAFE_UPDATES = 1;, and MySQL Workbench has it on by default.

SET SQL_SAFE_UPDATES = 1;
UPDATE topics SET views = 0;
UPDATE topics SET views = 0 WHERE views > 100;
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.

The second command is rejected too: it has a condition, but the views column is not part of any index. In safe update mode, UPDATE and DELETE run only if WHERE uses a key (WHERE id = 2) or the command has a LIMIT. If you see error 1175 in Workbench for a deliberate query, add a condition on a key column; disable the mode (SET SQL_SAFE_UPDATES = 0;) only for that specific command.

ORDER BY and LIMIT in UPDATE

In MySQL, a single-table UPDATE accepts ORDER BY and LIMIT: the command changes no more than the given number of rows in the given order. Reset the counter of the oldest thread:

UPDATE topics SET views = 0 ORDER BY created_at, id LIMIT 1;

LIMIT without ORDER BY picks arbitrary rows. This is a MySQL extension: in PostgreSQL UPDATE has neither ORDER BY nor LIMIT, and a multi-table UPDATE in MySQL does not allow them.

UPDATE with JOIN

When the condition depends on another table, MySQL lets you join it right in the UPDATE. Mark every message in Bob’s threads as archived:

UPDATE messages m
JOIN topics t ON t.id = m.topic_id
SET m.body = CONCAT('[archive] ', m.body)
WHERE t.author_id = 2;

Only the columns listed in SET change — here just messages.body. How table joins work is covered in the module lesson on SQL JOINs.

SQL DELETE: Removing Rows

The command DELETE FROM table WHERE condition; removes the rows that match the condition; without WHERE it removes every row, while the table itself with its columns and constraints stays. In MySQL, DELETE also accepts ORDER BY and LIMIT. AUTO_INCREMENT numbers of deleted rows are not reused.

DELETE FROM topic_tags WHERE topic_id = 3;
Query OK, 1 row affected (0.01 sec)

Thread 3 had one tag — “sport”, added in the INSERT ... SELECT section; now it has none. If a foreign key refers to the row being deleted, the result depends on the key’s action: DELETE FROM users WHERE id = 1 fails with error 1451 because Alice has threads, while deleting a thread deletes its messages by cascade. All the options are covered in Keys and Relationships Between Tables.

DELETE FROM tags; without a condition removes every tag (and, through ON DELETE CASCADE, every row of topic_tags); in safe update mode it is rejected with the same error 1175 as UPDATE. How emptying a table with DELETE differs from TRUNCATE TABLE is explained in TRUNCATE TABLE vs DELETE vs DROP.

Deleting in Batches with LIMIT

A single DELETE of millions of rows holds locks and grows the transaction log until the very end. On large tables, delete in chunks and repeat the command until it returns 0 rows affected:

DELETE FROM messages
WHERE author_id IS NULL
ORDER BY id
LIMIT 1000;

Each chunk is committed separately, and other users’ queries get a chance to run in between. In Java such a loop is written around executeUpdate(), which returns the number of deleted rows.

Run SELECT first, then UPDATE or DELETE

Before changing data, run a SELECT with the same WHERE and make sure it returns exactly the rows you expect. For a risky command, open a transaction: START TRANSACTION; DELETE FROM messages WHERE topic_id = 1; SELECT COUNT(*) FROM messages; — if the result is unexpected, ROLLBACK; brings the rows back; if everything is right, COMMIT;. Outside a transaction the mysql client works in autocommit mode, and every command is saved immediately.

Easy-to-Miss Mistakes in INSERT, UPDATE and DELETE

The most dangerous DML mistakes are the ones that pass without an error message: the command hit the wrong rows, a duplicate was silently skipped, related data was deleted. Below are the situations where beginners slip most often, what MySQL 8 does in each case and how to do it right.

Situation What happens How to do it right
UPDATE or DELETE without WHERE Every row of the table is changed or deleted Run a SELECT with the same condition first; --safe-updates in the client
INSERT INTO t VALUES (...) without a column list After ALTER TABLE ... ADD COLUMN — ERROR 1136: Column count doesn't match value count Always list the columns
WHERE phone = NULL Rows matched: 0 — a comparison with NULL is never true WHERE phone IS NULL
An apostrophe inside a string: 'O'Brien' ERROR 1064: You have an error in your SQL syntax Double it: 'O''Brien'; in Java — PreparedStatement parameters instead of string concatenation
A string in double quotes: "Dave" Works in MySQL by default, but in ANSI_QUOTES mode and in PostgreSQL it is a column name: Unknown column Strings only in single quotes
INSERT IGNORE “just in case” Rows with a foreign key violation are silently skipped, NULL turns into an empty string or zero ON DUPLICATE KEY UPDATE or handling error 1062 in code
REPLACE instead of an upsert The row is deleted: cascades fire, error 1451, without an explicit id the row gets a new number INSERT ... ON DUPLICATE KEY UPDATE
Reusing LAST_INSERT_ID() after the next insert The function already returns the id of another row; ERROR 1452 or a row linked to the wrong parent Save the id in a variable right after the INSERT you need

Frequently Asked Questions

Why does executeUpdate() in Java return 1 when the UPDATE changed nothing?

The MySQL server distinguishes matched rows from changed rows (Rows matched and Changed). The MySQL Connector/J driver connects with the CLIENT_FOUND_ROWS flag by default, so executeUpdate() returns the number of matched rows, not changed ones. To get changed rows, add useAffectedRows=true to the connection URL. The example with Changed: 0 is in the section SQL UPDATE.

How do I insert thousands of rows quickly from a Java application?

Use batch inserts: a PreparedStatement, addBatch() for each row and one executeBatch(), with autocommit turned off for the duration (setAutoCommit(false)). By default Connector/J still sends the rows as separate statements; the rewriteBatchedStatements=true connection URL parameter merges them into a multi-row INSERT and makes the insert several times faster. For millions of rows from a file, LOAD DATA INFILE is the fastest option.

What is a soft delete, and when is it better than DELETE?

With a soft delete the row is not removed but marked: you add a deleted_at DATETIME NULL column, run UPDATE ... SET deleted_at = NOW() instead of DELETE, and filter every query with WHERE deleted_at IS NULL. The data can be restored, the history is kept, and foreign keys do not block the deletion. The price: the table keeps growing, and unique keys also count the “deleted” rows. In Hibernate 6.4+ the @SoftDelete annotation enables this mode.

How do I do an upsert in PostgreSQL and other databases?

ON DUPLICATE KEY UPDATE is MySQL and MariaDB syntax only. PostgreSQL 9.5+ uses INSERT ... ON CONFLICT (username) DO UPDATE SET email = EXCLUDED.email, and ON CONFLICT DO NOTHING replaces INSERT IGNORE but suppresses only key conflicts. The standard SQL command for an upsert is MERGE: Oracle, SQL Server and PostgreSQL 15+ support it, MySQL does not. How the upsert works in MySQL is shown in the section Duplicate Keys.

Comments

Please log in or register to have a possibility to add comment.