Home/Learn/MySQL/DML — INSERT, UPDATE, DELETE

DML — INSERT, UPDATE, DELETE

Beginner

Fundamentals

Data Manipulation Language modifies rows; INSERT ... ON DUPLICATE KEY UPDATE, UPDATE with JOIN, and DELETE with LIMIT are common MySQL-specific DML patterns.

Overview

DML (Data Manipulation Language) statements read and modify data within a table. Unlike DDL, DML is transactional — changes can be committed or rolled back. MySQL extends standard SQL with useful DML variants: multi-row INSERT, INSERT ... ON DUPLICATE KEY UPDATE (upsert), REPLACE INTO, UPDATE with JOIN for correlated updates across tables, and DELETE with JOIN or LIMIT. Understanding these patterns is essential for efficient, correct data manipulation.

INSERT Variants

MySQL supports single-row, multi-row, and upsert INSERT patterns. INSERT IGNORE skips duplicate-key errors silently. INSERT ... ON DUPLICATE KEY UPDATE atomically inserts or updates.

SQL — INSERT variants

-- Single row
INSERT INTO products (name, price, stock)
VALUES ('Widget', 9.99, 100);

-- Multi-row (single statement = faster than N individual INSERTs)
INSERT INTO products (name, price, stock) VALUES
    ('Gadget',  19.99, 50),
    ('Doohickey', 4.99, 200),
    ('Thingamajig', 29.99, 10);

-- Upsert — insert or update on PK/unique key conflict
INSERT INTO product_stock (product_id, quantity)
VALUES (42, 100)
ON DUPLICATE KEY UPDATE
    quantity = quantity + VALUES(quantity);
-- Atomic: if product_id 42 exists, increments quantity by 100

-- INSERT IGNORE — skip rows that would violate unique constraint
INSERT IGNORE INTO product_views (product_id, user_id)
VALUES (42, 99);   -- no error if (42,99) already exists

-- INSERT ... SELECT — copy data between tables
INSERT INTO orders_archive SELECT * FROM orders
WHERE created_at < NOW() - INTERVAL 1 YEAR;

UPDATE & DELETE

UPDATE with JOIN modifies rows in one table based on conditions from another. DELETE with LIMIT prevents accidental full-table deletes and should be used in batch loops for large datasets.

SQL — UPDATE JOIN and batch DELETE

-- UPDATE with JOIN — update orders based on customer data
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.vip_discount = 0.10
WHERE c.tier = 'GOLD';

-- Multi-table UPDATE
UPDATE products p
JOIN inventory i ON p.id = i.product_id
SET p.in_stock = (i.quantity > 0);

-- Safe UPDATE — always include WHERE clause
-- Bad (updates EVERY row):
-- UPDATE products SET price = price * 1.1;
-- Good:
UPDATE products SET price = price * 1.1 WHERE category = 'Electronics';

-- DELETE with JOIN
DELETE o FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.status = 'DELETED' AND o.status = 'DRAFT';

-- Batch DELETE with LIMIT (avoids long lock)
DELETE FROM events WHERE created_at < '2023-01-01' LIMIT 5000;
-- Run in loop until affected_rows = 0

Transactions & Locking

DML inside an explicit transaction acquires row-level locks (InnoDB). SELECT ... FOR UPDATE locks rows for subsequent update in the same transaction, preventing concurrent modifications.

SQL — transactions, FOR UPDATE, optimistic locking

-- Explicit transaction
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If anything fails: ROLLBACK
COMMIT;

-- SELECT ... FOR UPDATE — pessimistic lock
START TRANSACTION;

SELECT quantity FROM inventory
WHERE product_id = 42
FOR UPDATE;                  -- locks the row; concurrent writers wait

UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 42 AND quantity > 0;

COMMIT;

-- Optimistic lock pattern (application-level, no row lock)
-- Read: SELECT id, version, quantity FROM inventory WHERE product_id = 42;
-- Write:
UPDATE inventory
SET quantity = quantity - 1, version = version + 1
WHERE product_id = 42 AND version = 7;   -- fails if row changed since read
-- If affected_rows = 0 → concurrent modification detected → retry

Key Points to Remember

1Multi-row INSERT is significantly faster than repeated single-row INSERTs.
2INSERT ... ON DUPLICATE KEY UPDATE provides atomic upsert on PK or unique key conflict.
3UPDATE with JOIN lets you update one table using conditions from another.
4Always include WHERE in UPDATE/DELETE — accidental full-table modifications are hard to undo.
5Batch DELETE with LIMIT prevents long-running locks on large tables.
6SELECT ... FOR UPDATE acquires a pessimistic row lock; use for read-modify-write patterns.

Interview Questions

What is the difference between INSERT IGNORE and INSERT ... ON DUPLICATE KEY UPDATE?

EasyTCS

How do you update rows in one table based on conditions in another table in MySQL?

MediumAmazon

What is a pessimistic lock and when would you use SELECT ... FOR UPDATE?

MediumFlipkart

Why is it risky to run DELETE without a WHERE clause and how do you protect against it?

EasyInfosys

Explain the optimistic locking pattern and how it differs from SELECT ... FOR UPDATE.

HardNetflix

Ask Aria about DML — INSERT, UPDATE, DELETE

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

DDL — CREATE, ALTER, DROP

SELECT Queries & Filtering