103 — Modifying Data

Beginner

Move beyond reading data to changing it. This SQLite-focused lesson covers inserting new rows, updating existing records, deleting safely, understanding SQLite's flexible type system, and working with ISO 8601 dates.

Learning Objectives

Insert single and multiple rows with INSERT INTO

Update existing rows safely with UPDATE and WHERE

Understand the danger of UPDATE and DELETE without WHERE

Delete specific rows with DELETE and WHERE

Know SQLite's data types: TEXT, INTEGER, REAL, BLOB, NULL

Understand SQLite's type affinity system

Work with dates using ISO 8601 format

Preview destructive changes safely before you run them in SQLite

Step 1

INSERT INTO — add a single row

Add a new product to the database. INSERT INTO is how new data enters your tables — every signup form, order placement, and data import uses INSERT behind the scenes.

Commands to Run

sqlite3 ecommerce.db

.headers on

.mode column

INSERT INTO products (name, description, price, category_id, stock_quantity, is_available) VALUES ('Wireless Earbuds Pro', 'Premium noise-cancelling wireless earbuds', 79.99, 1, 150, 1);

SELECT * FROM products ORDER BY id DESC LIMIT 3;

What This Does

INSERT INTO has two parts: the column list (which columns you're providing values for) and VALUES (the actual data). Columns you omit will use their default values — for example, `id` auto-increments and `created_at` defaults to the current timestamp. The column list and values must match in order and count: the first value goes into the first column, the second value into the second column, and so on.

Expected Outcome

The INSERT completes silently (no output means success in SQLite). The SELECT query confirms your new product appears as the last row, with an auto-generated id and created_at timestamp.

Pro Tips

1
Always list your columns explicitly — never rely on column order with INSERT INTO table VALUES (...)
2
Text values need single quotes; numbers don't
3
If INSERT fails, check for constraint violations: NOT NULL, UNIQUE, CHECK, or FOREIGN KEY

Common Mistakes to Avoid

⚠️Mismatching the number of columns and values — each column needs exactly one value
⚠️Using INSERT INTO products VALUES (...) without column names — fragile and breaks if table structure changes

Was this step helpful?