Inserting and Updating Data

In this lesson, you'll learn how to modify data in your database. We'll cover inserting new records, updating existing ones, and deleting data you no longer need.

What You'll Learn

By the end of this lesson you will load a small database with rows and then change and remove some of them on purpose — and you will be able to say how many rows each statement should touch before you run it, and check that against what the database reports. A write that hits more rows than you meant is not an error and produces no warning. The count on the N row(s) affected line is the only evidence you get, which makes reading it a habit rather than a detail.

This is the seed-data milestone of the warehouse project, and it is required for the final build: the capstone cannot report on a catalogue until something has put a correct catalogue there, and correcting a load that went in wrong is the same skill in reverse. You arrive able to filter rows precisely and to say what a condition does when a value is missing (Filtering and Sorting); what is new is that the same WHERE clause now decides which rows change, so being wrong about it costs data rather than just a wrong screenful.

The INSERT Statement

INSERT adds new rows to a table. There are several ways to insert data.

Insert Single Row

-- Create a users table
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL,
    age INTEGER
);

-- Insert a single user
INSERT INTO users (id, username, email, age)
VALUES (1, 'johndoe', 'john@example.com', 28);

-- Verify the insert
SELECT * FROM users ORDER BY id;

Insert Multiple Rows

-- Create a products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10, 2),
    category TEXT
);

-- Insert multiple products at once
INSERT INTO products (id, name, price, category)
VALUES
    (1, 'Laptop', 999.99, 'Electronics'),
    (2, 'Mouse', 24.99, 'Electronics'),
    (3, 'Desk', 299.99, 'Furniture'),
    (4, 'Chair', 199.99, 'Furniture');

-- View all products
SELECT * FROM products ORDER BY id;

Partial Inserts

You don't need to provide values for every column:

-- Create a blog posts table
CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    published_date TEXT,
    author TEXT
);

-- Insert with only required fields
INSERT INTO posts (id, title, author)
VALUES (1, 'My First Post', 'Alice');

-- Insert with all fields
INSERT INTO posts (id, title, content, published_date, author)
VALUES (2, 'SQL Tutorial', 'Learn SQL basics...', '2025-01-15', 'Bob');

-- Check the results
SELECT id, title, content, published_date, author FROM posts ORDER BY id;

Columns you leave out of the list get no value at all, which SQL records as NULL and this engine prints as None. That is not the same as an empty string: it is the absence of a value, and as you saw in the previous lesson it needs IS NULL rather than = to test for.

CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT);

INSERT INTO posts VALUES (1, 'First', 'Alice'), (2, 'Second', 'Bob');

INSERT INTO posts VALUES (3, 'Third', 'Alice');

UPDATE posts SET author = 'Carol' WHERE id = 2;

The UPDATE Statement

UPDATE modifies existing rows in a table. Always use a WHERE clause to specify which rows to update!

Update Single Column

-- Create an inventory table
CREATE TABLE inventory (
    id INTEGER PRIMARY KEY,
    product TEXT NOT NULL,
    quantity INTEGER,
    price DECIMAL(10, 2)
);

-- Insert initial data
INSERT INTO inventory (id, product, quantity, price)
VALUES
    (1, 'Laptop', 10, 999.99),
    (2, 'Mouse', 50, 24.99),
    (3, 'Keyboard', 30, 79.99);

-- Update the quantity of laptops
UPDATE inventory
SET quantity = 15
WHERE product = 'Laptop';

-- View the updated inventory
SELECT * FROM inventory ORDER BY id;

Update Multiple Columns

-- Create an employees table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    position TEXT,
    salary INTEGER,
    department TEXT
);

-- Insert employees
INSERT INTO employees (id, name, position, salary, department)
VALUES
    (1, 'John Doe', 'Developer', 60000, 'Engineering'),
    (2, 'Jane Smith', 'Developer', 65000, 'Engineering'),
    (3, 'Mike Johnson', 'Manager', 75000, 'Sales');

-- Promote John with a new position and salary
UPDATE employees
SET position = 'Senior Developer',
    salary = 75000
WHERE name = 'John Doe';

-- View the results
SELECT * FROM employees ORDER BY id;

Update with Calculations

-- Create a products table
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10, 2),
    discount_percent INTEGER
);

-- Insert products
INSERT INTO products (id, name, price, discount_percent)
VALUES
    (1, 'Laptop', 1000.00, 0),
    (2, 'Mouse', 50.00, 0),
    (3, 'Keyboard', 100.00, 0);

UPDATE products
SET discount_percent = 10,
    price = price * 0.9;

-- View discounted prices
SELECT id, name, price, discount_percent FROM products ORDER BY id;

The missing WHERE clause in that UPDATE is deliberate. An UPDATE with no WHERE changes every row in the table, and this is one of the rare cases where every row really is the target — a price change that applies to the whole catalogue. Everywhere else in this lesson, and in the safety rules at the end of it, a missing WHERE is the mistake being warned about; here it is the requirement.

The habit that keeps the difference safe is not "always write a WHERE". It is to decide the row count before running and then confirm it: three products here, so the pane should show two lines reading 3 row(s) affected — one from the INSERT that loaded them and one from the UPDATE that changed them. Any other number on the second line means the statement was not the one you thought you were running.

Modify it twice, and predict before each Run. First, add WHERE price > 100 to the update and predict both the affected-rows line and the resulting prices — one row matches, so the Laptop alone is discounted and the line reads 1 row(s) affected. Then change it to WHERE price > 10000 and predict the line: 0 row(s) affected, because nothing matched, and notice that a write matching nothing looks almost exactly like a write that succeeded.

Conditional Updates

-- Create a students table
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    grade TEXT,
    score INTEGER
);

-- Insert students
INSERT INTO students (id, name, grade, score)
VALUES
    (1, 'Alice', NULL, 95),
    (2, 'Bob', NULL, 82),
    (3, 'Charlie', NULL, 78),
    (4, 'Diana', NULL, 88);

-- Assign grades based on scores
UPDATE students SET grade = 'A' WHERE score >= 90;
UPDATE students SET grade = 'B' WHERE score >= 80 AND score < 90;
UPDATE students SET grade = 'C' WHERE score < 80;

-- View the results
SELECT * FROM students ORDER BY score DESC;

The DELETE Statement

DELETE removes rows from a table. Like UPDATE, always use WHERE to specify which rows to delete!

Delete Specific Rows

-- Create an orders table
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer TEXT NOT NULL,
    product TEXT,
    status TEXT,
    amount DECIMAL(10, 2)
);

-- Insert orders
INSERT INTO orders (id, customer, product, status, amount)
VALUES
    (1, 'Alice', 'Laptop', 'completed', 999.99),
    (2, 'Bob', 'Mouse', 'pending', 24.99),
    (3, 'Charlie', 'Keyboard', 'cancelled', 79.99),
    (4, 'Diana', 'Monitor', 'completed', 299.99);

-- Delete cancelled orders
DELETE FROM orders WHERE status = 'cancelled';

-- View remaining orders
SELECT * FROM orders ORDER BY id;

Delete with Multiple Conditions

-- Create a logs table
CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    message TEXT,
    level TEXT,
    created_date TEXT
);

-- Insert log entries
INSERT INTO logs (id, message, level, created_date)
VALUES
    (1, 'App started', 'INFO', '2025-01-01'),
    (2, 'Error occurred', 'ERROR', '2025-01-02'),
    (3, 'Warning issued', 'WARN', '2025-01-03'),
    (4, 'Old error', 'ERROR', '2024-12-15'),
    (5, 'Debug message', 'DEBUG', '2025-01-04');

-- Delete old error logs from 2024
DELETE FROM logs
WHERE level = 'ERROR' AND created_date < '2025-01-01';

-- View remaining logs
SELECT * FROM logs ORDER BY id;

Important Safety Tips

Warning: Be very careful with UPDATE and DELETE!

Without a WHERE clause, these commands affect ALL rows:

-- DANGEROUS: Updates all rows!
UPDATE products SET price = 0;

-- DANGEROUS: Deletes all rows!
DELETE FROM products;

Pro Tip: Before running UPDATE or DELETE, run a SELECT with the same WHERE clause to see which rows will be affected!

-- First, check which rows will be updated
SELECT * FROM employees WHERE department = 'Sales';

-- Then, if it looks right, do the update
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';

That dry run is worth its keystrokes because the SELECT and the UPDATE share the one thing that can be wrong — the WHERE clause — and only one of them is reversible. Count the rows the SELECT returns, then check the UPDATE reports the same number.

Order of Writes Decides the Answer

A read is repeatable: run the same SELECT twice against an unchanged table and you get the same rows. A write is not. Once statements change data, where a statement sits in the script is part of what it means, and moving a line gives you a different — and equally error-free — result.

CREATE TABLE stock (sku TEXT PRIMARY KEY, units INTEGER NOT NULL);

The empty table above is already created; the four lines below stock it, ship from it, and report.

Where the Values Come From

Everything written so far puts values you typed into the statement yourself. A real application inserts what a user supplied: a name from a signup form, a quantity from a cart, an address from an import file. This is the widest injection surface in SQL — a SELECT built badly leaks data, but an INSERT, UPDATE or DELETE built badly changes or destroys it.

Never assemble a statement by gluing a value into the query text. Write the statement once with placeholders and hand the values over separately, as a parameterized query:

INSERT INTO customers (id, name, city) VALUES (?, ?, ?);

UPDATE orders SET status = ? WHERE id = ?;

Each ? is a slot the database knows about before it ever sees the value. Whatever arrives there stays a value: a name containing a quote mark, a semicolon or an entire DROP TABLE statement is stored as that text and cannot become part of the statement, because the statement was already parsed and planned without it.

You cannot run these here. The Run button sends a script of literal SQL with no program around it, and binding a parameter is something a program does through its database driver — so there is nothing on this page to bind with, and these fences are shapes to recognise rather than statements to execute. Two caveats come with them:

• The binding must happen in the driver. A library helper that escapes a value and pastes it into your string is still string-building, and it is only as correct as its author's model of the database's quoting rules. Hand the value to the driver.
• A placeholder stands for a value, never for an identifier. UPDATE ? SET ... and ORDER BY ? do not work, because the database must know which table and column it is touching before it can plan anything. When a table or column name genuinely comes from outside, check it against an allow-list you wrote by hand and reject anything else.

When a Write Touches the Wrong Rows

A wrong WHERE in a SELECT shows you the wrong rows; a wrong WHERE in an UPDATE changes them. Neither is an error, and the affected-rows count is the only thing that tells you which happened.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL, price_cents INTEGER NOT NULL);
INSERT INTO products VALUES (1,'Laptop Pro','Electronics',129999),(2,'Laptop Air','Electronics',89999),(3,'Desk Lamp','Furniture',3999),(4,'Laptop Stand','Accessories',4999);
UPDATE products SET price_cents = price_cents / 2 WHERE name LIKE 'Laptop%';
SELECT id, name, price_cents FROM products ORDER BY id;

4 row(s) affected
2 row(s) affected
id | name | price_cents
-----------------------
1 | Laptop Pro | 64999
2 | Laptop Air | 44999
3 | Desk Lamp | 3999
4 | Laptop Stand | 4999

Try It Yourself

Here is the build task. The starter creates two tables, loads customers, and then stops — the orders are missing, and the two corrections the warehouse needs have not been made. Work the four TODOs until the final listing shows the two orders that survive.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status TEXT NOT NULL, total_cents INTEGER NOT NULL);
INSERT INTO customers VALUES (1,'Ada','Oslo'),(2,'Bo','Bergen'),(3,'Cy','Tromso');
-- TODO 1: load three orders with ONE INSERT statement, in this order:
--         id 10 for customer 1, status pending, total 2500
--         id 11 for customer 2, status pending, total 4000
--         id 12 for customer 1, status cancelled, total 900
-- TODO 2: mark every pending order as shipped. Two rows should change.
-- TODO 3: remove the cancelled order. One row should go.
-- TODO 4: write your three statements in this gap, above the SELECT.
--         The SELECT below reports whatever is there when it runs, so it
--         must stay last: right now it runs against an empty orders table.
SELECT id, customer_id, status, total_cents FROM orders ORDER BY id;

3 row(s) affected
3 row(s) affected
2 row(s) affected
1 row(s) affected
id | customer_id | status | total_cents
---------------------------------------
10 | 1 | shipped | 2500
11 | 2 | shipped | 4000

Once it passes, try two variations, predicting the result before each Run. First, change TODO 2's condition to WHERE status = 'Pending' with a capital P and predict the affected-rows line — it becomes 0 row(s) affected, because stored text values are compared exactly, and the final listing then shows both orders still pending. Then restore it and move the DELETE above the UPDATE: predict whether the final listing changes at all, and why the answer is no here but would not be if the update had targeted cancelled rows.

CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT NOT NULL, status TEXT NOT NULL);
INSERT INTO a VALUES (2, 'Y', 'pending');

UPDATE a SET status = 'shipped' WHERE id = 1;
SELECT id, name, status FROM a ORDER BY id;

CREATE TABLE b (id INTEGER PRIMARY KEY, name TEXT NOT NULL, status TEXT NOT NULL);
INSERT INTO b VALUES (2, 'Y', 'pending');

DELETE FROM b WHERE id = 1;
INSERT INTO b VALUES (1, 'X', 'shipped');
SELECT id, name, status FROM b ORDER BY id;

Practice Exercises

Try these exercises using the tables above:

1. Insert a new employee with your name and details
2. In the multiple-row INSERT example, cut the price of the two Electronics products by 5% with a single UPDATE, and check that the affected-rows line says 2 before you look at the prices. (That table has a category column and no discount_percent; the table in the calculations example is the other way round, so pick your target table before you write the statement.)
3. Delete all orders with an amount less than $50
4. Insert three new students with different scores, then assign their grades
5. Update the price of a specific product, increasing it by 20%

Key Takeaways

• INSERT adds new rows to a table
• You can insert one row or multiple rows at once
• UPDATE modifies existing rows - always use WHERE to target specific rows
• DELETE removes rows - always use WHERE unless you want to delete everything
• Test your WHERE clause with SELECT before running UPDATE or DELETE
• Updates can use calculations and expressions
• Never run UPDATE or DELETE without WHERE unless you're absolutely sure!

Next Steps

You now have the full CRUD toolkit — Create, Read, Update, Delete. But real databases rarely store everything in a single table. The next lesson introduces JOINs, which let you combine data from multiple related tables in a single query. Understanding joins is the key to working with normalized databases where customers, orders, and products each live in their own table.