INSERT, UPDATE, DELETE - Manipulating Data

What Is It?

The Three Core DML Commands

So far you have built tables and seeded a few rows. Real applications constantly write new rows, update existing ones, and occasionally delete them. This chapter covers the three DML commands you will write hundreds of times a week as a backend developer:

• INSERT INTO — add new rows
• UPDATE — change existing rows
• DELETE FROM — remove rows

All three are DML (Data Manipulation Language) — transactional, rollback-able, and triggered by your application whenever data changes. The most important rule of this chapter: UPDATE and DELETE without a WHERE clause touch every row in the table. This is the single most common way juniors destroy production data. Please internalize it.

Sample Data for This Chapter

We will use a students table with this initial data:

Why Does It Matter?

Why INSERT, UPDATE, DELETE Deserve Respect

1. Every Write in Your App Eventually Becomes DML

User signs up → an INSERT. User changes their password → an UPDATE. Admin deletes a spam account → a DELETE. The ORM (Django, Hibernate, Sequelize) you'll use at work is just a fancy wrapper that generates DML under the hood. When things go wrong, you drop into raw SQL and need to read/write these commands fluently.

2. A Missing WHERE Is the #1 Production Disaster

The horror stories are real:

• A developer ran UPDATE users SET password = '...'; without WHERE — every user had the same password.
• Another ran DELETE FROM orders; forgetting the WHERE — the company lost 3 days of orders.
• GitLab in 2017 accidentally dropped production data with a misplaced command.

The fix is cultural: always write WHERE first, always test on a single row, always wrap destructive statements in a transaction so you can ROLLBACK.

3. INSERT/UPDATE Performance at Scale

Writing 10 rows: anything works. Writing 10 million rows: multi-row INSERT is 100x faster than 10 million single INSERTs. Updating 1 million rows: batching in chunks of 10,000 prevents table locks and replication lag. These patterns separate hobbyists from engineers who can handle scale.

4. Upserts Are Asked in Every Backend Interview

"How do you insert a new row if it doesn't exist, and update it if it does?" The MySQL answer is INSERT ... ON DUPLICATE KEY UPDATE. Knowing the three ways (INSERT IGNORE, ON DUPLICATE KEY UPDATE, REPLACE INTO) and when to use each is a classic interview differentiator.

Detailed Explanation

Detailed Explanation

1. INSERT — Four Ways to Add Rows

Form 1: All Columns in Order

INSERT INTO students VALUES (6, 'Diya Kapoor', 'Chennai', 88, 1);

Values must match the column order of the table definition. Fragile: if a new column is added later, this breaks.

Form 2: Specify Columns Explicitly (RECOMMENDED)

INSERT INTO students (id, name, city, marks, active)
VALUES (6, 'Diya Kapoor', 'Chennai', 88, 1);

Safer and self-documenting. Rearranging or adding columns later doesn't break the statement.

Form 3: Multi-Row INSERT

INSERT INTO students (id, name, city, marks) VALUES
    (6, 'Diya Kapoor',  'Chennai',   88),
    (7, 'Arjun Mehta',  'Ahmedabad', 81),
    (8, 'Sneha Patel',  'Jaipur',    90);

One round-trip to the server, often 10-100x faster than three separate INSERTs. The active column isn't specified and will use its DEFAULT (or NULL).

Form 4: INSERT ... SELECT (Copy Data From Another Table)

-- Copy high-scoring students into a separate table.
INSERT INTO top_students (id, name, marks)
SELECT id, name, marks FROM students WHERE marks >= 90;

Powerful for data migrations, archival, and populating reporting tables. No VALUES keyword — the SELECT provides all rows.

2. UPDATE — Changing Existing Rows

Basic UPDATE

UPDATE students
SET marks = 90
WHERE id = 3;

Rohan's marks change from 78 to 90. One row affected.

Update Multiple Columns in One Statement

UPDATE students
SET marks = 95, active = 1
WHERE id = 5;

Both columns change for Vikram in a single statement. Comma-separated assignments.

Update Using an Expression

-- Give every active student a 5-mark bonus.
UPDATE students
SET marks = marks + 5
WHERE active = 1;

You can reference the current column value in the SET clause. Each row gets its own current marks + 5.

The CRITICAL Warning

-- WITHOUT WHERE: updates EVERY row in the table!
UPDATE students SET marks = 0;

This is catastrophic on any non-trivial table. MySQL has a safe-update mode (enable via SET SQL_SAFE_UPDATES = 1;) that rejects UPDATE/DELETE without a WHERE on a key column. Turn this on in dev environments as training wheels.

3. DELETE — Removing Rows

Basic DELETE

DELETE FROM students WHERE id = 5;

Removes Vikram. One row affected.

DELETE With Range

DELETE FROM students WHERE marks < 70;

Removes every student scoring under 70.

The Same CRITICAL Warning

-- WITHOUT WHERE: deletes EVERY row!
DELETE FROM students;

The table still exists (unlike DROP), but every row is gone. On a big table this can take minutes and fills the transaction log. If you truly want to empty a table, use TRUNCATE TABLE students; — faster and resets AUTO_INCREMENT.

4. DELETE vs TRUNCATE vs DROP Recap

• DELETE: transactional, supports WHERE, slow on big tables, doesn't reset AUTO_INCREMENT.
• TRUNCATE: fast, no WHERE, resets AUTO_INCREMENT, implicit commit (cannot rollback).
• DROP: removes the table itself.

Full comparison is in Chapter 4.

5. INSERT IGNORE — Silently Skip Duplicates

INSERT IGNORE INTO students (id, name, city, marks)
VALUES (1, 'Someone Else', 'Kolkata', 77);

If id=1 already exists (duplicate PK), MySQL ignores the insert and emits a warning instead of an error. Useful for bulk imports where you don't care about duplicates. Warning: IGNORE silences other errors too (type mismatches, CHECK violations may become warnings). Use with care.

6. ON DUPLICATE KEY UPDATE — The True Upsert

INSERT INTO students (id, name, city, marks)
VALUES (1, 'Aarav Sharma', 'Mumbai', 90)
ON DUPLICATE KEY UPDATE marks = VALUES(marks);

If id=1 does not exist, it inserts a new row. If id=1 exists, it updates marks = 90. The VALUES(col) function refers to the value from the VALUES clause. This is the recommended "upsert" pattern in MySQL.

MySQL 8.0.19+ replaces VALUES() with a row alias:

INSERT INTO students (id, name, city, marks)
VALUES (1, 'Aarav Sharma', 'Mumbai', 90) AS new
ON DUPLICATE KEY UPDATE marks = new.marks;

7. REPLACE INTO — Delete-Then-Insert

REPLACE INTO students (id, name, city, marks)
VALUES (1, 'Aarav Sharma', 'Mumbai', 95);

If id=1 exists, REPLACE deletes the old row and inserts the new one. If it doesn't exist, it just inserts. Simpler syntax than ON DUPLICATE KEY UPDATE, but has two big gotchas:

1. Any columns you don't specify are reset to their DEFAULT (or NULL). So REPLACE can silently wipe data you forgot to list.
2. Delete-then-insert means the AUTO_INCREMENT id jumps, triggers fire twice (DELETE and INSERT), and any FKs with ON DELETE CASCADE cascade.

Prefer ON DUPLICATE KEY UPDATE in 90% of cases.

Code Examples

-- Setup
CREATE DATABASE IF NOT EXISTS school;
USE school;
DROP TABLE IF EXISTS students;
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    city VARCHAR(30),
    marks INT DEFAULT 0,
    active TINYINT DEFAULT 1
);

-- Form 1: all columns in order (fragile)
INSERT INTO students VALUES (1, 'Aarav Sharma', 'Mumbai', 87, 1);

-- Form 2: explicit columns (recommended)
INSERT INTO students (id, name, city, marks, active)
VALUES (2, 'Priya Iyer', 'Bengaluru', 92, 1);

-- Form 3: multi-row insert (fastest for bulk)
INSERT INTO students (id, name, city, marks) VALUES
    (3, 'Rohan Verma',  'Delhi',     78),
    (4, 'Ananya Reddy', 'Hyderabad', 95),
    (5, 'Vikram Singh', 'Pune',      65);

SELECT * FROM students;

-- Vikram made it active and scored higher in the retest.
UPDATE students
SET marks = 88, active = 1
WHERE id = 5;

SELECT id, name, marks, active FROM students WHERE id = 5;

-- Give every Mumbai student a 5-mark bonus.
UPDATE students
SET marks = marks + 5
WHERE city = 'Mumbai';

SELECT id, name, city, marks FROM students WHERE city = 'Mumbai';

-- Enable safe update mode (workbench default). Rejects UPDATE without
-- a WHERE on a key column.
SET SQL_SAFE_UPDATES = 1;

-- Attempt: forgot WHERE — would zero out every student.
UPDATE students SET marks = 0;

-- Disable it back (do this only when you REALLY mean to touch every row).
SET SQL_SAFE_UPDATES = 0;

-- Delete one specific student
DELETE FROM students WHERE id = 5;

-- Delete everyone with marks below 80
DELETE FROM students WHERE marks < 80;

SELECT * FROM students;

-- Create an archive table that holds inactive or low-scoring students.
DROP TABLE IF EXISTS students_archive;
CREATE TABLE students_archive LIKE students;

-- Copy all inactive students into the archive.
INSERT INTO students_archive
SELECT * FROM students WHERE active = 0;

-- After archival, remove them from the live table.
DELETE FROM students WHERE active = 0;

SELECT COUNT(*) AS archived FROM students_archive;
SELECT COUNT(*) AS live     FROM students;

-- Counting page views per student: increment if row exists, insert if new.
DROP TABLE IF EXISTS page_views;
CREATE TABLE page_views (
    student_id INT PRIMARY KEY,
    view_count INT NOT NULL DEFAULT 0
);

-- First visit for student 1: insert
INSERT INTO page_views (student_id, view_count)
VALUES (1, 1)
ON DUPLICATE KEY UPDATE view_count = view_count + 1;

-- Second visit for student 1: update
INSERT INTO page_views (student_id, view_count)
VALUES (1, 1)
ON DUPLICATE KEY UPDATE view_count = view_count + 1;

-- First visit for student 2: insert
INSERT INTO page_views (student_id, view_count)
VALUES (2, 1)
ON DUPLICATE KEY UPDATE view_count = view_count + 1;

SELECT * FROM page_views;

Common Mistakes

-- Meant to raise Priya's marks, forgot WHERE:
UPDATE students SET marks = 95;

UPDATE students SET marks = 95 WHERE id = 2;

-- Meant to remove one inactive student:
DELETE FROM students;

DELETE FROM students WHERE id = 5;
-- OR if you truly want to empty the table, use TRUNCATE:
TRUNCATE TABLE students;

-- students table order: (id, name, city, marks, active)
INSERT INTO students VALUES (6, 'Chennai', 'Diya Kapoor', 88, 1);

INSERT INTO students (id, name, city, marks, active)
VALUES (6, 'Diya Kapoor', 'Chennai', 88, 1);

-- students table has (id, name, city, marks, active)
-- You only want to update Aarav's marks:
REPLACE INTO students (id, marks) VALUES (1, 90);

-- Use UPDATE for targeted changes:
UPDATE students SET marks = 90 WHERE id = 1;

-- Or specify ALL columns in REPLACE:
REPLACE INTO students (id, name, city, marks, active)
VALUES (1, 'Aarav Sharma', 'Mumbai', 90, 1);

Summary