Inserting, Updating and Deleting Data

Every chapter of the SQL for Beginners series so far has only read data: SELECT is safe to run a hundred times because it changes nothing. Chapter 7 is about the three statements that do change it — INSERT adds rows, UPDATE rewrites them and DELETE removes them. Together they are the entire write side of SQL, and they all reward the same habit: know exactly which rows you are about to touch before you press Enter. Every result below is printed with sqlite3’s two display dot-commands, which change how rows are laid out on screen and never touch the data:

sqlite3 store.db
sqlite> .headers on
sqlite> .mode column

One more ground rule for this chapter: each block opens with BEGIN; and closes with ROLLBACK;, so your sample tables finish the chapter exactly as chapters 1–6 left them. That is not decoration — chapter 21 explains how this safety net works, and it is the reason you can copy and run everything here without wrecking your practice database.

Adding rows with INSERT

The column list after the table name is optional but worth typing: it says which columns you are filling and in which order the values arrive. Columns you leave out fall back to their DEFAULT value, or to NULL when they have none. The id column needs no value at all — because it is an INTEGER PRIMARY KEY, SQLite hands the new row one more than the current maximum. Name your columns explicitly, so a table that gains a column next year will not break an insert written today, and confirm the write with a SELECT repeating the condition you believe you used — an INSERT reports nothing but success, so the row itself is the proof.

BEGIN;
INSERT INTO customers (first_name, last_name, email, city, country, signup_date)
VALUES ('Rohan', 'Mehta', 'rohan.mehta@example.com', 'Pune', 'India', '2026-03-15');

SELECT id, first_name, city, signup_date FROM customers WHERE id = 7;
ROLLBACK;

Expected output:

id  first_name  city  signup_date
--  ----------  ----  -----------
7   Rohan       Pune  2026-03-15

Inserting many rows at once

A single statement may carry several value tuples; each one is a row, and the whole statement either lands or does not. This matters on busy tables: one round trip instead of six, and one place for a typo to hide.

BEGIN;
INSERT INTO products (name, category, price, stock) VALUES
    ('Refactoring', 'Tech', 699, 20),
    ('Diary of a Wimpy Kid', 'Kids', 299, 35);

SELECT id, name, price, stock FROM products WHERE id > 6 ORDER BY id;
ROLLBACK;

Expected output:

id  name                  price  stock
--  --------------------  -----  -----
7   Refactoring           699    20
8   Diary of a Wimpy Kid  299    35

MySQL and PostgreSQL accept exactly this multi-row VALUES spelling, so the syntax is portable as written. The other way to fill a table is with a query instead of literals — INSERT INTO archive (id, name) SELECT id, name FROM products WHERE price < 300; copies rows across in one go, and the SELECT can be anything you learned in chapters 3 to 6. SQLite counts the rows any write touched with changes(), which the next two sections lean on.

Changing rows with UPDATE

UPDATE names one table, lists assignments after SET and filters them with WHERE. The right-hand side is an expression, so prices move relative to themselves and several columns can be assigned in one statement by separating them with commas.

BEGIN;
UPDATE products
SET price = price - 50
WHERE category = 'Fiction';

SELECT changes();
SELECT name, price FROM products WHERE category = 'Fiction';
ROLLBACK;

Expected output:

changes()
---------
1
name                price
------------------  -----
The Silent Patient  449

Read changes() as your early warning system: 1 matches the one Fiction book, so the filter did what you meant. Write the same statement with no WHERE and it will cheerfully report 6 — every row in the table. The clause you must never forget is the one the database will never demand. For updates that involve a second table the dialects split: MySQL writes UPDATE products JOIN discounts d ON d.category = products.category SET price = price - d.off;, while PostgreSQL and SQLite put the extra table after FROM. SQLite has supported that form since version 3.33, and the next section uses it.

Removing rows with DELETE

Deletion deserves a rehearsal. Run the very same condition as a SELECT first to count the victims, then delete under a transaction, undo the damage and confirm the table is whole:

SELECT id, status FROM orders WHERE status = 'cancelled';

BEGIN;
DELETE FROM orders WHERE status = 'cancelled';
SELECT changes();
ROLLBACK;
SELECT COUNT(*) AS orders FROM orders;

Expected output:

id  status
--  ---------
3   cancelled
changes()
---------
1
orders
------
6

Notice what that delete would have cost outside the transaction: order 3 vanishes, but its two rows in order_items stay behind, pointing at an order that no longer exists. Chapter 9, Keys and Referential Integrity, is where the database starts refusing exactly that mistake. The dangerous variant deserves one rehearsal before you move on: DELETE FROM orders; with no WHERE at all is legal, silent and irreversible outside a transaction — run it once inside BEGIN;…ROLLBACK; and watch all six rows vanish and come back. A transaction is the only undo button SQLite offers; without one, the recovery is your backup.

Duplicate-proof inserts

Real data arrives twice: a form is double-submitted, a file is imported again. SQLite offers two answers. INSERT OR IGNORE skips the row silently if any constraint complains, and ON CONFLICT ... DO UPDATE turns the clash into an update — an upsert.

BEGIN;
INSERT OR IGNORE INTO customers (first_name, last_name, email, city, country, signup_date)
VALUES ('Rohan', 'Mehta', 'aarav@example.com', 'Pune', 'India', '2026-03-15');
SELECT changes();

INSERT INTO customers (first_name, last_name, email, city, country, signup_date)
VALUES ('Aarav', 'Sharma', 'aarav@example.com', 'Mumbai', 'India', '2026-03-20')
ON CONFLICT(email) DO UPDATE SET city = excluded.city;
SELECT id, first_name, city FROM customers WHERE email = 'aarav@example.com';
ROLLBACK;

Expected output:

changes()
---------
0
id  first_name  city
--  ----------  ------
1   Aarav       Mumbai

The first insert matched Aarav’s unique email, so SQLite ignored it and changes() reported 0. The second matched the same constraint and updated instead: the keyword excluded names the row you tried to insert, so excluded.city is its city. Two caveats travel with this feature. The conflict target must be a PRIMARY KEY or UNIQUE column or SQLite answers ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint; and other databases spell it differently — MySQL uses ON DUPLICATE KEY UPDATE, PostgreSQL uses ON CONFLICT just like SQLite. And beware OR IGNORE: it swallows every constraint failure, including the ones you wanted to hear about.

Updating from another table

The last pattern of the chapter borrows values from a second table. A temporary discounts table decides how far each category drops, and SQLite’s UPDATE ... FROM matches the two on a condition:

BEGIN;
CREATE TABLE discounts (category TEXT PRIMARY KEY, off INTEGER);
INSERT INTO discounts VALUES ('Tech', 100), ('Kids', 50);

UPDATE products
SET price = price - d.off
FROM discounts d
WHERE products.category = d.category;

SELECT name, price FROM products WHERE category IN ('Tech', 'Kids') ORDER BY id;
ROLLBACK;

Expected output:

name                         price
---------------------------  -----
The Very Hungry Caterpillar  199
Clean Code                   799
Python Crash Course          649
Matilda                      299
Robotics for Kids            599

Five rows changed — the two Tech books down by 100, the three Kids books by 50 — the one Fiction book untouched, and the ROLLBACK restores every price for the chapters that follow. Where a second table would rather be a query than a join, reach for a subquery in the WHERE — WHERE category IN (SELECT ...) does the same job, and chapter 13 makes that idiom routine.

Common mistakes

• Leaving the WHERE clause off an UPDATE or DELETE — the statement is then legal and universal; it rewrites or removes every row in the table, and SQLite will not pause to ask.
• Guessing which auto-generated id you just received — run SELECT last_insert_rowid(); in the same session instead of assuming the next number.
• Using INSERT OR IGNORE as a catch-all — it hides NOT NULL and CHECK failures along with duplicates, so a bad row can disappear with no message at all.
• Confusing excluded with the target table — inside DO UPDATE, excluded.city is the incoming value and customers.city is what is stored now.
• Deleting a parent row while its children exist — orders own order items; without foreign keys switched on you create orphans silently (chapter 9 fixes this).
• Trusting a write you have not read — run the same condition as a SELECT first; changes() tells you how many rows moved, not which.

Practice checklist

1. Insert a customer of your own, confirm the row with a SELECT, then ROLLBACK and prove with COUNT(*) that six customers remain.
2. Write an UPDATE that raises every Tech book by 50, check changes(), and undo it inside a transaction.
3. Delete the cancelled order using a SELECT you ran first, then restore it with ROLLBACK.
4. Run an ON CONFLICT(email) upsert that changes Aarav’s city, and confirm the new value before rolling back.
5. Rewrite the MySQL-style UPDATE products JOIN discounts ... statement using SQLite’s FROM clause and check that the same four rows change.

What's next?

You can put rows in, move them around and take them out — but nothing yet stops you from storing a price of -5 or an email of NULL. Chapter 8 hands that job to the database in Data Types and Constraints.