INSERT, UPDATE, DELETE & transactions

Reading data is only half the job. Applications also create, modify and remove rows, and they must never leave the database half-updated. This lesson covers the data-modification statements and the transactions that keep them safe.

INSERT

INSERT INTO customers (name, city, email)
VALUES ('Kavya Nair', 'Kochi', 'kavya@example.com');

-- Several rows in one statement (much faster than one INSERT per row)
INSERT INTO customers (name, city, email) VALUES
  ('Rohit Jain', 'Jaipur', 'rohit@example.com'),
  ('Sana Sheikh', 'Lucknow', 'sana@example.com');

-- Copy rows from a query
INSERT INTO archived_orders (id, customer_id, total)
SELECT id, customer_id, total FROM orders WHERE created_at < '2025-04-01';

Always list the column names. If someone later adds a column, an INSERT without a column list breaks.

Upserts in MySQL

When a row may already exist, INSERT ... ON DUPLICATE KEY UPDATE updates it instead of failing on the unique key.

INSERT INTO stock (sku, qty) VALUES ('SAREE-RED-01', 5)
ON DUPLICATE KEY UPDATE qty = qty + VALUES(qty);

VALUES() inside this clause still works in MySQL 8 but is deprecated; the newer form uses a row alias: INSERT INTO stock (sku, qty) VALUES ('SAREE-RED-01', 5) AS new ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty;

UPDATE and DELETE

UPDATE products
SET price = ROUND(price * 1.05, 2)
WHERE category = 'Dry fruits';

DELETE FROM carts
WHERE updated_at < NOW() - INTERVAL 30 DAY;

Before running an UPDATE or DELETE, run the same WHERE clause as a SELECT COUNT(*) to see how many rows you will touch. MySQL Workbench's safe updates mode even refuses statements without a key-based WHERE.

Transactions and ACID

A transaction groups statements so that either all succeed or none do. InnoDB tables support this.

START TRANSACTION;

UPDATE accounts SET balance = balance - 2500 WHERE id = 1;   -- Meena pays
UPDATE accounts SET balance = balance + 2500 WHERE id = 2;   -- supplier receives
INSERT INTO ledger (from_id, to_id, amount) VALUES (1, 2, 2500);

COMMIT;      -- or ROLLBACK; to undo everything since START TRANSACTION

Letter	Property	Meaning
A	Atomicity	All statements succeed or none do
C	Consistency	Constraints hold before and after
I	Isolation	Concurrent transactions don't see each other's half-done work
D	Durability	Once committed, data survives a crash

SAVEPOINT name marks a point you can return to with ROLLBACK TO SAVEPOINT name without abandoning the whole transaction.

Locking and isolation

InnoDB's default isolation level is REPEATABLE READ. When you read a row you intend to change, lock it:

START TRANSACTION;
SELECT qty FROM stock WHERE sku = 'LADDU-500G' FOR UPDATE;
-- application checks qty >= requested
UPDATE stock SET qty = qty - 2 WHERE sku = 'LADDU-500G';
COMMIT;

Without FOR UPDATE, two cashiers could both read qty = 2 and both sell two boxes.

Common mistakes

• Running UPDATE or DELETE without a WHERE clause.
• Leaving a transaction open in a console session, holding locks for minutes.
• Mixing MyISAM tables into transactions — MyISAM ignores ROLLBACK.
• Remember that DDL such as ALTER TABLE or TRUNCATE causes an implicit commit in MySQL.